MERGE 정확성과 성능: Source 유일성·조건부 DML·DELETE WHERE

핵심 요약

MERGE는 Source Row를 읽고 ON 조건으로 Target과 대응시킨 뒤, Match Row에는 UPDATE, Nonmatch Row에는 INSERT를 수행하는 집합 기반 DML입니다. DELETE WHERE를 함께 사용하면 MERGE가 실제로 갱신한 Match Row를 갱신 후 값으로 검사해 삭제할 수도 있습니다.

MERGE는 결정적 문장입니다. 같은 Target Row를 한 MERGE 문장에서 여러 번 갱신할 수 없으므로, Update Branch가 가능한 업무에서는 Source를 ON Key당 한 행으로 만들어야 합니다. Target의 업무 Key에는 Unique Constraint를 두어 동시 Session의 중복 Insert 경쟁까지 최종적으로 차단해야 합니다.

정확한 진단 순서는 다음과 같습니다.

Target Grain과 업무 Key 정의
→ Source Grain과 중복 검증
→ ON 조건으로 Match·Nonmatch 분류
→ UPDATE WHERE·INSERT WHERE 적용
→ UPDATE된 Row에 DELETE WHERE 적용
→ Constraint·Trigger·Error Logging·Commit 정책 검증
→ 실제 Row 수·Redo·Undo·Wait 실측

학습 목표

1. Source·Target·ON 조건의 역할을 구분한다.
2. Source 중복이 MERGE 결정성을 깨뜨리는 이유를 설명한다.
3. Target Unique Constraint와 동시성 경쟁을 구분한다.
4. UPDATE WHERE와 INSERT WHERE의 적용 집합을 판단한다.
5. DELETE WHERE의 대상과 평가 시점을 설명한다.
6. ON 조건에 사용한 Target Column 변경 제한을 이해한다.
7. ON (0=1)의 Constant Filter 의미를 설명한다.
8. DML Error Logging의 동작과 기록되지 않는 오류를 구분한다.
9. 불필요한 변경을 줄이면서도 Source·Target 접근 비용은 실측해야 함을 이해한다.
10. MERGE와 분리 DML 중 업무 원자성·오류·재시작 정책에 맞는 방식을 선택한다.

1. MERGE의 판단 구조

MERGE INTO customer t
USING customer_stage s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET
    t.customer_name = s.customer_name,
    t.grade         = s.grade
WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, grade)
  VALUES (s.customer_id, s.customer_name, s.grade);

각 요소의 역할은 다음과 같습니다.

ON 조건은 단순한 사후 Filter가 아닙니다. 업무적으로 같은 Entity를 찾는 기준이며, 너무 좁거나 변동 가능한 속성을 포함하면 기존 Row를 Nonmatch로 잘못 분류할 수 있습니다.

예를 들어 Target의 CUSTOMER_ID가 Unique인데 ON 조건에 상태까지 포함하면, 같은 고객이 이미 존재해도 상태가 다르다는 이유로 Nonmatch가 되어 Insert를 시도하고 Unique 오류가 발생할 수 있습니다.

ON (t.customer_id = s.customer_id
AND t.status_cd   = s.status_cd)

업무 식별 Key와 Branch Filter를 구분해야 합니다. 상태에 따라 Update 여부를 제한하려는 목적이라면 UPDATE WHERE 등 더 적합한 위치를 검토합니다.

2. Source 유일성과 결정성

MERGE는 같은 Target Row를 한 문장에서 여러 번 갱신할 수 없는 결정적 문장입니다. Target이 CUSTOMER_ID당 한 행이고 Match Row를 갱신한다면, Source도 MERGE 시점에 CUSTOMER_ID당 한 행이어야 합니다.

2.1 중복 진단

SELECT customer_id, COUNT(*) AS source_count
FROM   customer_stage
GROUP BY customer_id
HAVING COUNT(*) > 1;

다음 Source는 Target 한 행에 서로 다른 두 값을 제안합니다.

Target: customer_id=10, grade='C'
Source 1: customer_id=10, grade='A'
Source 2: customer_id=10, grade='B'

→ 같은 Target Row를 두 번 갱신하려는 구조
→ MERGE 결정성 위반 및 ORA-30926 위험

Target의 Unique Constraint는 Source 중복을 정리하지 않습니다. Source 유일성은 입력 결정성, Target Unique Constraint는 저장 결과의 무결성을 담당하는 서로 다른 통제입니다.

2.2 대표 Row 선택

업무상 최신 Row 한 건을 선택한다면 정렬 기준과 유일한 Tie-Breaker를 함께 사용합니다.

USING (
  SELECT customer_id, customer_name, grade, source_time, source_id
  FROM (
    SELECT s.*,
           ROW_NUMBER() OVER (
             PARTITION BY customer_id
             ORDER BY source_time DESC, source_id DESC
           ) AS rn
    FROM customer_stage s
  )
  WHERE rn = 1
) s

source_time이 같은 Row가 있을 수 있으므로 source_id 같은 유일한 Tie-Breaker가 필요합니다. Tie-Breaker가 없으면 실행마다 대표 Row가 달라질 수 있습니다.

업무 규칙이 집계라면 KEEP (DENSE_RANK ...), MAX, SUM 등을 사용할 수 있지만, 단순히 오류를 없애기 위해 임의 집계를 넣어서는 안 됩니다.

USING (
  SELECT customer_id,
         MAX(grade) KEEP (
           DENSE_RANK LAST ORDER BY source_time, source_id
         ) AS grade
  FROM customer_stage
  GROUP BY customer_id
) s

3. Target 유일성과 동시성

MERGE 한 문장은 현재 읽은 일관된 상태를 기준으로 Branch를 결정하지만, 두 Session이 동시에 같은 신규 Key를 Nonmatch로 판단하는 경쟁까지 없애는 것은 아닙니다.

ALTER TABLE customer
ADD CONSTRAINT customer_uk UNIQUE (customer_id);

Target Unique Constraint는 다음을 보장합니다.

• 같은 업무 Key의 최종 중복 저장 방지
• 동시 Insert 경쟁 시 하나의 Session만 성공하도록 강제
• 오류 또는 Lock 대기를 Application의 재시도 정책과 연결

다만 Unique Constraint가 Source 중복을 자동 제거하거나, MERGE가 항상 대기 없이 성공하도록 만드는 것은 아닙니다. 동시 실행 시에는 TX Lock 대기나 Unique 오류가 발생할 수 있으므로 다음을 정해야 합니다.

1. 재시도 가능한 오류 범위
2. 재시도 시 Source Batch의 Idempotency Key
3. 최대 재시도 횟수와 Backoff
4. Commit 전후 성공 상태 확인 방법
5. 동일 Batch 재실행 시 중복 방지 규칙

4. UPDATE WHERE: Match 이후 실제 변경 대상

MERGE INTO customer t
USING customer_stage s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET
    t.grade      = s.grade,
    t.updated_at = SYSTIMESTAMP
  WHERE DECODE(t.grade, s.grade, 0, 1) = 1;

UPDATE WHERE는 ON 조건으로 Match된 Row 중 실제 Update할 Row를 제한합니다. 조건은 Source와 Target Column을 모두 참조할 수 있습니다.

UPDATE WHERE가 FALSE이면 다음과 같이 처리됩니다.

ON 조건 Match
→ UPDATE WHERE FALSE
→ Update 생략
→ Nonmatch Insert Branch로 이동하지 않음
→ DELETE WHERE의 대상도 되지 않음

값이 같은 Row의 Update를 생략하면 Table·Index 변경, Undo·Redo, Row Trigger 실행을 줄일 수 있습니다. 그러나 Source와 Target을 읽고 Join하는 비용까지 자동으로 사라지는 것은 아니므로 Buffers, A-Rows, Redo 크기와 실제 변경 건수를 함께 비교해야 합니다.

NULL-safe 비교

WHERE t.grade <> s.grade

한쪽 값이 NULL이면 비교 결과가 UNKNOWN이 될 수 있어 변경을 놓칠 수 있습니다. 다음처럼 Data Type과 업무 규칙에 맞는 NULL-safe 조건을 사용합니다.

WHERE DECODE(t.grade, s.grade, 0, 1) = 1

또는 명시적으로 NULL 경우를 분리합니다.

WHERE t.grade <> s.grade
   OR (t.grade IS NULL AND s.grade IS NOT NULL)
   OR (t.grade IS NOT NULL AND s.grade IS NULL)

5. INSERT WHERE: Nonmatch Source의 추가 Filter

WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, grade)
  VALUES (s.customer_id, s.customer_name, s.grade)
  WHERE s.active_yn = 'Y'

INSERT WHERE는 Target에 Match가 없는 Source Row 중 실제 Insert할 Row를 제한합니다. 이 조건은 Source Column만 참조할 수 있습니다.

ON 조건 Nonmatch
→ INSERT WHERE TRUE: Insert
→ INSERT WHERE FALSE: 아무 작업도 하지 않음

INSERT WHERE가 FALSE라고 해서 Update Branch로 이동하거나 오류가 발생하지 않습니다.

6. DELETE WHERE: Update된 Match Row만 삭제

MERGE INTO inventory t
USING inventory_stage s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN
  UPDATE SET t.quantity = s.quantity
  WHERE s.apply_yn = 'Y'
  DELETE WHERE t.quantity = 0
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity)
  VALUES (s.product_id, s.quantity);

평가 순서는 다음과 같습니다.

ON 조건 Match
→ UPDATE WHERE 평가
→ 조건이 TRUE인 Row만 UPDATE
→ 갱신된 Target 값으로 DELETE WHERE 평가
→ DELETE WHERE가 TRUE이면 해당 Update Row 삭제

핵심 규칙은 다음과 같습니다.

• DELETE 대상은 MERGE가 실제로 Update한 Match Row뿐입니다.
• DELETE 조건은 Update 전 원본 값이 아니라 Update 후 Target 값을 평가합니다.
• UPDATE WHERE가 FALSE여서 Update되지 않은 Match Row는 Delete 대상이 아닙니다.
• Source와 Match되지 않은 기존 Target Row는 Delete 대상이 아닙니다.
• WHEN NOT MATCHED에서 새로 Insert된 Row는 Delete 대상이 아닙니다.

Source에 없는 Target 전체를 삭제하려면 별도의 Anti Join DELETE, Direct-Join DELETE, Staging 교체 전략 등 업무에 맞는 동기화 절차를 사용합니다.

7. ON 조건에 사용한 Target Column 변경 제한

다음 MERGE에서 t.customer_id는 Match 판단에 사용됩니다.

ON (t.customer_id = s.customer_id)

같은 MERGE의 Update Clause에서 t.customer_id를 변경할 수 없습니다. Match Key를 변경해야 한다면 다음 대안을 검토합니다.

• 별도의 UPDATE 문장
• 기존 Row DELETE 후 새 Key로 INSERT
• 변경 전 Key와 변경 후 Key를 Source에서 분리한 단계적 처리
• 업무 Key가 아니라 불변 Surrogate Key로 Match한 뒤 업무 속성 변경

ON Key는 가능한 한 불변 식별자로 설계하는 것이 안전합니다.

8. Constant Filter ON (0=1)

MERGE INTO customer t
USING customer_stage s
ON (0 = 1)
WHEN NOT MATCHED THEN
  INSERT (customer_id, customer_name, grade)
  VALUES (s.customer_id, s.customer_name, s.grade);

항상 거짓인 Constant Filter를 사용하면 모든 Source Row가 Nonmatch가 되며, Optimizer는 Target Join 없이 Unconditional Insert로 처리할 수 있습니다.

이는 Update Clause를 생략한 일반 MERGE와 다릅니다. Update Clause만 생략해도 Match 여부를 판단하기 위한 Join은 필요할 수 있지만, ON (0=1)은 Join 자체가 필요 없음을 명시합니다.

단순 적재라면 INSERT SELECT가 더 명확할 수 있으므로 다음을 비교합니다.

• 문법의 의도와 유지보수성
• Direct-Path Insert 필요 여부
• Error Logging과 Returning 사용 방식
• 실제 실행계획과 Redo·Undo

9. Trigger와 Branch별 동작

• Update Branch가 실행되면 Target의 Update Trigger가 실행됩니다.
• Insert Branch가 실행되면 Insert Trigger가 실행됩니다.
• Update 후 DELETE WHERE가 TRUE인 Row에는 Delete Trigger도 실행됩니다.
• UPDATE WHERE가 FALSE인 Match Row에는 실제 Update가 없으므로 Row Update Trigger를 줄일 수 있습니다.

Trigger 내부 SQL이 있다면 Source Row 수가 아니라 실제 Branch별 변경 Row 수 × Trigger 내부 작업량으로 비용을 추정해야 합니다.

10. DML Error Logging의 범위와 제한

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name     => 'CUSTOMER',
    err_log_table_name => 'ERR$_CUSTOMER'
  );
END;
/

MERGE INTO customer t
USING customer_stage s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET t.grade = s.grade
WHEN NOT MATCHED THEN
  INSERT (customer_id, grade)
  VALUES (s.customer_id, s.grade)
LOG ERRORS INTO err$_customer ('MERGE_BATCH_20260802')
REJECT LIMIT 100;

MERGE의 error_logging_clause는 INSERT의 Error Logging과 같은 동작을 사용합니다. Error Table의 주요 제어 Column은 다음과 같습니다.

REJECT LIMIT의 기본값은 0입니다. 허용 오류 수를 초과하면 Statement가 종료되고 변경이 Rollback될 수 있으므로, Error Table 건수와 Target 변경 상태를 함께 확인해야 합니다. Parallel DML에서는 Reject Limit가 각 Parallel Server에 적용됩니다.

Error Logging으로 처리되지 않는 대표 상황

다음 오류는 Error Logging을 사용해도 Statement를 종료하고 Rollback시킬 수 있습니다.

• Deferred Constraint 위반
• Direct-Path Insert 또는 MERGE의 Unique Constraint·Unique Index 위반
• MERGE Update Branch에서 발생한 Unique Constraint·Unique Index 위반
• Error Table이 지원하지 않는 Column Type 구조로 잘못 정의된 경우

반대로 일부 Row 오류와 특정 ORA-30926은 Error Table에 기록될 수 있으므로, "Error Logging이 모든 오류를 살린다" 또는 "ORA-30926은 항상 기록되지 않는다"처럼 단정하지 않습니다. 실제 오류 유형과 SQL 수행 방식을 확인해야 합니다.

Error Logging은 성공 Row를 자동 Commit하지 않습니다. 다음 운영 정책을 별도로 정합니다.

1. 성공 Row의 Commit 시점
2. 오류 Row 수정·재처리 절차
3. 동일 Batch 재실행 시 중복 방지
4. Error Tag와 Batch ID 관리
5. Reject Limit 초과 시 Target·Error Table 정합성 확인

11. 성능 설계: 변경 건수와 접근 건수를 분리

MERGE의 성능은 문법 하나로 결정되지 않습니다.

11.1 Source 준비

• ON Key당 한 행으로 축약
• Source Filter를 가능한 한 조기에 적용
• 불필요한 Column 제거
• 대표 Row 선택 Sort·Aggregate 비용 확인

11.2 Target 접근

• ON Key를 지원하는 Index 검토
• 업무 Key Unique Constraint로 무결성 보장
• Match 비율에 따른 Nested Loop·Hash Join 비용 비교
• Partition Pruning 가능 여부 확인

11.3 실제 변경 비용

• Update Row 수
• Insert Row 수
• Delete Row 수
• 유지되는 Index 수
• Row Trigger 실행 횟수
• Undo·Redo 크기
• Lock 대기와 Commit 정책

UPDATE WHERE로 변경 Row가 줄어도 Join 입력과 Source 준비 비용이 그대로일 수 있습니다. 반대로 Source 사전 집계는 결정성을 확보하지만 Sort·Hash Aggregate 비용을 추가합니다. 전체 Runtime 통계로 판단합니다.

12. MERGE와 분리 DML 선택

MERGE가 적합한 경우

• Source가 Branch별로 동일한 Data Set을 사용
• Update·Insert를 하나의 업무 원자 단위로 처리
• Match·Nonmatch 규칙이 명확
• Source 유일성을 한 Query Block에서 보장
• Branch별 Error·Commit 정책이 동일

분리 DML이 더 명확할 수 있는 경우

• Update와 Insert의 Source 조건·Join이 크게 다름
• Source에 없는 Target 전체를 별도 삭제해야 함
• Branch별 Commit·오류·재시작 정책이 다름
• 각 Branch를 다른 운영 시간이나 병렬 정책으로 수행
• 일부 Branch만 Error Logging 또는 Direct-Path가 필요

두 문장으로 분리하면 중간 상태와 동시성 창이 생길 수 있으므로 Transaction 경계와 재시작 절차를 반드시 설계합니다.

13. 실행계획과 Runtime 검증 절차

1. Source의 ON Key 중복을 사전에 조회합니다.
2. Target의 Unique Constraint와 Index 구성을 확인합니다.
3. 소량의 대표 Data로 Match·Nonmatch·Update Skip·Delete 결과를 계산합니다.
4. 실제 Cursor의 A-Rows, Starts, Buffers, A-Time을 확인합니다.
5. Session Statistic Delta로 Redo·Undo와 Execute 수를 비교합니다.
6. Trigger 실행 횟수와 Trigger 내부 SQL을 확인합니다.
7. Error Table의 ORA_ERR_OPTYP$, 오류 번호, Tag를 분석합니다.
8. 동시 실행에서 TX Lock 대기·Unique 오류·재시도 결과를 확인합니다.
9. Target 최종 Row 수와 업무 Key 중복을 검증합니다.
10. 동일 Batch 재실행으로 Idempotency를 시험합니다.

혼동하기 쉬운 판단