집합 기반 DML 선택: MERGE·조인 갱신·Multi-Table INSERT

핵심 요약

집합 기반 DML을 선택할 때 가장 먼저 결정할 것은 문법이 아니라 다음 세 가지입니다.

1. 실제로 변경할 Base Table은 어느 Table인가?
2. Target 한 행과 Source 한 행의 업무 Grain은 무엇인가?
3. 같은 Target Row가 한 문장 안에서 두 번 이상 변경될 가능성이 있는가?

한 Table의 기존 행을 단순 조건으로 변경
→ 일반 UPDATE·DELETE

한 Source를 한 Target에 적재
→ INSERT SELECT

다른 Table과 조인해 한 Target Table 갱신
→ UPDATE ... FROM 또는 결정적인 Join View UPDATE

같은 Source로 기존 행 갱신 + 신규 행 삽입
→ MERGE

한 Source Row를 여러 Target Table로 분배
→ Multi-Table INSERT

한 SQL로 합쳤다는 사실만으로 성능과 정합성이 자동 보장되지는 않습니다. Source·Target Key의 유일성, 실제 변경 행 수, Index·Constraint·Trigger, Undo·Redo와 Lock 유지시간을 함께 검증합니다.

학습 목표

1. 일반 DML·Direct-Join UPDATE·Join View UPDATE·MERGE·Multi-Table INSERT의 목적을 구분한다.
2. Source Grain과 Target Grain으로 중복 변경 위험을 판별한다.
3. Oracle 26ai의 UPDATE ... FROM 제약과 ORA-30926 발생 조건을 설명한다.
4. Join View의 Key Preservation과 결정적 Update 조건을 구분한다.
5. MERGE의 ON 조건과 MATCHED·NOT MATCHED 분류를 설명한다.
6. MERGE의 ON Column Update 제한과 DELETE WHERE 평가 순서를 설명한다.
7. INSERT ALL과 INSERT FIRST의 Branch 실행 차이를 계산한다.
8. Multi-Table INSERT의 Target·Sequence·Parallel·RETURNING 제한을 설명한다.
9. 집합 기반 DML이 줄이는 Call과 줄이지 못하는 Row별 변경비용을 구분한다.
10. 결과 건수·중복·NULL·Constraint·재시작 가능성을 실측한다.

1. 문법보다 먼저 확정할 세 가지

1.1 변경 대상 Base Table

한 DML 문장은 기본적으로 변경 책임이 명확해야 합니다.

Join View를 통한 DML도 한 문장 안에서 여러 Base Table을 동시에 변경할 수 없습니다.

1.2 Source Grain과 Target Grain

고객별 포인트를 갱신한다면 Source는 고객당 최대 한 행이어야 합니다.

Target Grain: CUSTOMER_ID당 한 행
Source Grain: CUSTOMER_ID당 한 행
Join Key: Target.CUSTOMER_ID = Source.CUSTOMER_ID

거래 상세가 고객당 여러 행이라면 먼저 집계·순위·중복 제거 규칙으로 Source Grain을 맞춥니다.

SELECT customer_id,
       SUM(point_delta) AS point_delta
FROM   point_stage
GROUP BY customer_id;

1.3 0건·1건·다건·NULL

• Source 0건: 일반적으로 변경 없음
• Source Key당 1건: 결정적인 갱신·삽입 가능
• Source Key당 다건: MERGE나 Direct-Join UPDATE에서 동일 Target Row 중복 변경 오류 가능
• NULL Key: 일반적인 = 조건에서 NULL끼리도 TRUE가 되지 않음
• Target Key 중복: 업무 Grain 자체가 깨질 수 있으므로 Unique Constraint 검토

2. 가장 단순한 일반 DML부터 검토

한 Table을 단순 Predicate로 변경할 수 있다면 일반 DML이 가장 명확합니다.

UPDATE customer
SET    grade = 'VIP'
WHERE  annual_amount >= 10000000;

INSERT INTO sales_archive
       (sale_id, customer_id, sale_date, amount)
SELECT sale_id, customer_id, sale_date, amount
FROM   sales
WHERE  sale_date < DATE '2025-01-01';

장점은 다음과 같습니다.

• 변경 대상과 Predicate가 명확함
• 불필요한 Source Join과 중복을 피하기 쉬움
• 실행계획과 실제 변경 행 수를 설명하기 쉬움
• 업무적으로 필요 없는 MERGE Branch를 만들지 않음

MERGE가 문법적으로 더 복잡하다는 이유만으로 더 빠르거나 더 느리다고 단정하지 않습니다. Source 구성, Access Path, Join, 변경 행 수와 부가 Object 비용을 실측합니다.

3. Direct-Join UPDATE: UPDATE ... FROM

Oracle AI Database 26ai의 UPDATE는 FROM 절을 이용해 다른 Table과 직접 조인할 수 있습니다.

UPDATE employee e
SET    e.salary = e.salary * (1 + g.raise_rate)
FROM   salary_grade g
WHERE  g.grade = e.grade
AND    g.raise_rate > 0;

3.1 핵심 규칙

• 변경되는 Column은 Target인 employee의 Column이어야 합니다.
• FROM 절의 Table은 값 제공과 행 선택에 사용합니다.
• 같은 Target Row가 조인 결과에 두 번 이상 나타나면 ORA-30926이 발생합니다.
• Target Table은 한 개만 지정합니다.
• Target과 Source 사이에 Right·Full Outer Join은 허용되지 않습니다.
• Target Trigger는 일반 UPDATE와 동일하게 실행됩니다.

따라서 SALARY_GRADE.GRADE에 중복이 있으면 한 직원이 여러 Source Row와 연결되어 오류가 발생할 수 있습니다. Unique Constraint 또는 사전 집계로 결정성을 확보합니다.

3.2 Source가 여러 Table일 때

FROM 절 내부 Source Table끼리는 ANSI Join을 사용할 수 있지만, Target Table 자체는 UPDATE 절에 하나만 둡니다.

UPDATE employee e
SET    e.bonus = b.rate * e.salary
FROM   bonus_rule b
JOIN   department d
  ON   d.deptno = b.deptno
WHERE  e.deptno = d.deptno
AND    b.active_yn = 'Y';

Source Join 결과가 Target Row당 한 행인지 반드시 확인합니다.

4. Join View UPDATE와 Key Preservation

Join View를 갱신할 때는 한 Base Table만 변경하고, 각 Base Row를 한 번만 변경하는 결정성이 필요합니다.

UPDATE (
    SELECT e.salary,
           g.raise_rate
    FROM   employee e
    JOIN   salary_grade g
      ON   g.grade = e.grade
)
SET salary = salary * (1 + raise_rate)
WHERE raise_rate > 0;

4.1 Key-Preserved Table

Key-Preserved Table은 Base Table의 Key가 Join 결과에서도 Key로 유지되는 Table입니다. 예를 들어 EMPLOYEE를 SALARY_GRADE.GRADE의 Unique Key와 조인하면 Employee 한 행이 Join 결과에 최대 한 번 나타날 수 있습니다.

Key Preservation은 Join View의 Modifiability를 이해하는 핵심 개념이며 특히 INSERT에서는 중요합니다.

4.2 Oracle 21c 이후 UPDATE 판단

Oracle 21c 이후 Join View의 UPDATE는 모든 갱신 Column이 Key-Preserved Table에 속해야만 하는 것은 아닙니다. 다음 조건이 핵심입니다.

• 한 DML이 한 Base Table만 변경
• 같은 Base Row를 한 번만 변경하는 결정적인 Update
• WITH CHECK OPTION 등 View 제한을 위반하지 않음

즉 Key Preservation만 기계적으로 확인하는 것이 아니라 실제 Update의 결정성을 함께 확인해야 합니다.

4.3 INSERT·DELETE와의 차이

• Join View INSERT: 삽입 Column은 Key-Preserved Table에서 와야 하며 WITH CHECK OPTION View에는 제한이 있음
• Join View UPDATE: 한 Base Table만 변경하고 각 Row를 한 번만 변경해야 함
• Join View DELETE: Key-Preserved Table 구성에 따라 삭제 Base Table이 결정됨

실제 수정 가능 여부는 USER_UPDATABLE_COLUMNS에서 확인할 수 있습니다.

SELECT column_name, updatable, insertable, deletable
FROM   user_updatable_columns
WHERE  table_name = 'EMP_SALARY_VIEW';

5. MERGE: 같은 Source로 갱신과 삽입

MERGE INTO customer_point t
USING (
    SELECT customer_id,
           SUM(point_delta) AS point_delta
    FROM   point_stage
    GROUP BY customer_id
) s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET t.point = t.point + s.point_delta
WHEN NOT MATCHED THEN
  INSERT (customer_id, point)
  VALUES (s.customer_id, s.point_delta);

ON 조건은 각 Source Row를 다음 두 경우로 분류합니다.

Target Match 존재
→ WHEN MATCHED

Target Match 없음
→ WHEN NOT MATCHED

5.1 MERGE는 결정적인 문장

Oracle은 MERGE를 결정적인 문장으로 정의합니다. 같은 Target Row를 한 MERGE에서 여러 번 갱신할 수 없습니다. Source의 ON Key가 중복되어 같은 Target Row와 여러 번 연결되면 ORA-30926 위험이 있습니다.

따라서 Source를 Target Key당 한 행으로 만들고, Target에는 업무 Key의 Unique Constraint를 두는 것이 안전합니다.

5.2 ON 조건 Column은 갱신할 수 없음

MERGE의 UPDATE SET에서는 ON 조건에 사용한 Target Column을 갱신할 수 없습니다.

-- ON에서 t.customer_id를 사용했다면
-- UPDATE SET t.customer_id = ... 는 허용되지 않음

업무 Key 자체를 변경해야 한다면 별도의 DML 설계와 Constraint·참조 무결성 검토가 필요합니다.

5.3 MATCHED UPDATE의 WHERE

WHEN MATCHED THEN UPDATE ... WHERE는 이미 Match된 Row 중 실제 Update를 수행할 Row를 추가로 제한합니다. 조건이 FALSE이면 해당 Source Row가 NOT MATCHED Branch로 이동하는 것이 아니라 Update를 건너뜁니다.

5.4 DELETE WHERE의 평가 순서

MERGE의 DELETE WHERE는 다음 규칙을 가집니다.

• MERGE에서 Match되어 실제 Update 대상이 된 Target Row만 검사
• UPDATE SET이 적용된 변경 후 값으로 Delete 조건 평가
• ON 조건에 Match되지 않은 기존 Target Row는 삭제하지 않음

MERGE INTO account t
USING account_stage s
ON (t.account_id = s.account_id)
WHEN MATCHED THEN
  UPDATE SET t.balance = t.balance + s.delta
  DELETE WHERE t.balance = 0;

여기서 Delete 조건은 Update 이후의 t.balance를 평가합니다.

5.5 무조건 Insert 목적

ON (0=1)처럼 상수 FALSE 조건을 사용하면 Oracle은 Join 없이 모든 Source Row를 Insert하는 형태로 처리할 수 있습니다. 단순 적재라면 일반 INSERT SELECT가 더 읽기 쉬운지도 함께 비교합니다.

6. Multi-Table INSERT: 한 Source를 여러 Target으로 분배

Multi-Table INSERT는 하나의 Source Subquery가 반환한 각 Row를 하나 이상의 Target Table에 삽입합니다.

6.1 INSERT ALL

조건부 ALL은 각 Source Row에 대해 모든 WHEN을 평가하고, TRUE인 모든 Branch의 INTO 목록을 실행합니다.

INSERT ALL
  WHEN amount >= 1000000 THEN
    INTO high_value_sales(sale_id, amount)
    VALUES (sale_id, amount)
  WHEN region = 'SEOUL' THEN
    INTO seoul_sales(sale_id, amount)
    VALUES (sale_id, amount)
SELECT sale_id, amount, region
FROM   sales_stage;

한 Row가 두 조건을 모두 만족하면 두 Table에 모두 삽입됩니다.

6.2 INSERT FIRST

FIRST는 위에서부터 WHEN을 평가해 처음 TRUE인 Branch의 INTO 목록만 실행하고, 그 Row에 대한 나머지 WHEN은 건너뜁니다.

INSERT FIRST
  WHEN amount >= 1000000 THEN
    INTO high_value_sales(sale_id, amount)
    VALUES (sale_id, amount)
  WHEN amount >= 100000 THEN
    INTO medium_value_sales(sale_id, amount)
    VALUES (sale_id, amount)
  ELSE
    INTO low_value_sales(sale_id, amount)
    VALUES (sale_id, amount)
SELECT sale_id, amount
FROM   sales_stage;

조건이 겹치면 Branch 순서가 분류 결과를 결정합니다.

6.3 Branch 표현식 범위

WHEN 조건과 VALUES에서 사용할 값은 Source Subquery의 Select List에 노출되어야 합니다. 복잡한 표현식이나 Table Alias가 필요하면 Source Select List에서 Alias를 부여한 뒤 사용합니다.

INSERT ALL
  WHEN amount_with_tax >= 1100000 THEN
    INTO high_value_sales(sale_id, amount)
    VALUES (sale_id, amount_with_tax)
SELECT sale_id,
       amount * 1.1 AS amount_with_tax
FROM   sales_stage;

6.4 주요 제한

Multi-Table INSERT는 다음 제한을 확인합니다.

• Target은 Table만 가능하며 View·Materialized View는 대상이 될 수 없음
• Remote Table을 Target으로 사용할 수 없음
• Source Subquery에서 Sequence를 사용할 수 없음
• Target 중 IOT 또는 Bitmap Index 보유 Table이 있으면 병렬화되지 않음
• RETURNING 절을 사용할 수 없음
• 조건부 Insert는 최대 127개의 WHEN 절을 가질 수 있음

7. 목적별 선택 표

8. 집합 기반 DML의 성능 의미

100,000행 Row-by-Row Client 처리
→ Execute·Network User Call 최대 100,000회

한 번의 UPDATE·MERGE·INSERT SELECT
→ 큰 집합을 한 SQL Execute로 전달 가능

집합 기반 DML이 줄일 수 있는 대표 비용은 다음과 같습니다.

• SQL Execute Call
• PL/SQL·SQL Engine Context Switch
• Client·Server Network Round Trip
• 동일 Source를 여러 번 조회하는 중복 작업

그러나 다음 Row별 Database 작업은 영향 Row 수에 따라 계속 발생합니다.

• Table Row와 Index Entry 변경
• Constraint와 Trigger 실행
• Undo와 Redo 생성
• Row·Table Lock 유지
• Data Type 변환과 업무 표현식 계산

따라서 Call 감소와 전체 Elapsed Time 감소를 같은 의미로 단정하지 않습니다.

9. 혼동하기 쉬운 판단

10. 진단과 적용 절차

1. 변경 Target Base Table과 Column을 확정합니다.
2. Target 한 행 Grain과 Source 한 행 Grain을 문서화합니다.
3. Source Key별 COUNT(*)와 Target Unique Constraint를 확인합니다.
4. 일반 DML로 표현 가능한지 먼저 판단합니다.
5. 조인 갱신은 UPDATE ... FROM과 Join View 방식의 결정성을 확인합니다.
6. 동기화는 MERGE의 ON Key·ON Column Update 제한·Branch 조건을 확인합니다.
7. 다중 적재는 ALL·FIRST·ELSE의 Row별 Branch 수를 계산합니다.
8. NULL·0건·다건·중복 Source·중복 Target Sample로 결과를 검증합니다.
9. 실행계획의 Source·Target E-Rows·A-Rows, Join Method와 Access Path를 확인합니다.
10. 실제 변경 행 수, Branch별 Insert 건수, Trigger·Constraint 오류를 대조합니다.
11. redo size, Undo 사용량, Execute·User Call, Elapsed Time을 같은 구간에서 측정합니다.
12. Unique Constraint·Batch ID·재시작 조건으로 운영 정합성을 보호합니다.