SQL 튜닝이론 - MERGE 문 활용법 (MERGE INTO)

 

SQL 튜닝이론 - MERGE 문 활용법 (MERGE INTO)

 

DW(Data Warehouse )에서 가장 흔히 발생하는 오퍼레이션은 새로운 기간에 발생한 신규 트렌잭션 데이터를 기존 테이블에 반영하는 작업 즉, 두 시스템 간 데이터를 동기화 하는 작업입니다.

 

-- 전일 발생한 변경 데이터를 기간계 시스템으로부터 추출합니다.

create table customer\_delta

as

select * from customer

where mod_dt >= trunc(sysdate) - 1

and mod_dt < trunc(sysdate);

-- 생성한 테이블을 DW 시스템으로 전송합니다. 이때 MERGE 문을 활용합니다.

merge into customer t using customer_delta s

on ( t.cust_id = s.cust_id)

when matched then update

  set t.cust_nm = s.cust_nm , t.email = s.email ...

when not matched then insert

(cust_id, cust_nm, ...) values (s.cust_id, s.cust_nm ...)

 

MERGE 문은 SOURCE 테이블을 기준으로 TARGET 테이블과 LEFT OUTER 방식으로 조인해서 조인에 성공하면 UPDATE 실패하면 새로운 레코드를 INSERT 합니다. MERGE 문을 UPSERT 라고 부르는 이유입니다.

 

OPTIONAL CLAUSES

 

UPDATE 와 INSERT를 선택적으로 처리할 수 있습니다.

 

merge into customer t using customer_delta s

on ( t.cust_id = s.cust_id)

when matched then update

  set t.cust_nm = s.cust_nm , t.email = s.email ... ;

merge into customer t using customer_delta s

on ( t.cust_id = s.cust_id)

when not matched then insert

(cust_id, cust_nm, ...) values (s.cust_id, s.cust_nm ...)

 

옵션 기능을 통해 수정가능 조인 뷰 기능을 대체할 수 있습니다.

 

update
(
  select d.deptno, d.avg_sal as d_avg_sal, e.avg_sal as e_avg_sal
  from
  -- 부서별 평균 급여 (1)
  ( select deptno, round(avg(sal), 2) avg_sal
    from emp 
    group by deptno
  ) e
  , dept d (1)
  where d.deptno = e.deptno
)
set d_avg_sal = e_avg_sal;

merge into dept d
using (
    select deptno , round(avg(sal), 2) avg_sal
    from emp 
    group by deptno
) e
on (d.deptno = e.deptno)
when matched then update
set d.avg_sal = e.avg_sal

 

CONDITIONAL OPERATION

 

 

ON 절에 기술한 조인문 외에 추가적으로 조건절을 기술할 수도 있습니다.

 

 

MERGE INTO CUSTOMER T
USING CUSTOMER_DELTA S 
ON (T.CUST_ID = S.CUST_ID)
WHEN MATCHED THEN UPDATE
    SET T.CUST_NM = S.CUST_NM, T.EMAIL = S.EMAIL ...
    WHERE REG_DT >= TO_DATE('20000101', 'YYYYMMDD')
WHEN NOT MATCH THEN INSERT 
    (CUST_ID, CUST_NM, ... ) VALUES
    (S.CUST_ID, S.CUST_NM ... )
    WHERE REG_DT < TRUNC(SYSDATE)

 

DELETE CLAUSE

 

 

이미 지정된 데이터를 조건에 따라 지우는 기능도 제공합니다.

 

MERGE INTO CUSTOMER T
USING CUSTOMER_DELTA S 
ON (T.CUST_ID = S.CUST_ID)
WHEN MATCHED THEN 
UPDATE SET T.CUST_NM = S.CUST_NM, T.EMAIL = S.EMAIL ...
DELETE WHERE T.WITHDRAW_DT IS NOT NULL
WHEN NOT MATCH THEN INSERT 
    (CUST_ID, CUST_NM, ... ) VALUES
    (S.CUST_ID, S.CUST_NM ... )

 

 

UPDATE 가 이루어진 결과를 기준으로 탈퇴일시가 NULL이 아닌 레코드를 삭제합니다. 즉, 탈퇴일시가 NULL이 아니었더라도 MERGE 결과가 NULL 이면 삭제하지 않습니다.

 

DELETE 는 조인에 성공한 데이터만 삭제할 수 있습니다. SOURCE(CUSTOMER_DELTA) 테이블에서 삭제된 데이터는 TARGET(CUSTOMER) 테이블에서도 지워야하는데 MERGE 문으로는 할 수 없습니다. SOURCE에서 삭제된 데이터는 TARGET의 레코드와 조인할 수 없기 때문입니다. 조인하지 못한 TARGET의 데이터는 UPDATE,DELETE 할 수 없습니다. 조인에 성공한 데이터를 모두 UPDATE 하고서 그 결과 값이 DELETE 조건에 만족하면 삭제하는 기능입니다.

 

MERGE INTO 활용 예

 

저장하려는 레코드가 기존에 있으면 UPDATE 하고, 그렇지 않으면 INSERT 하는 요건을 수행햐려합니다. 개별 SQL로 처리하려면 SQL을 항상 두 번 수행해야 합니다.

 

-- :VAL1 : DEPTNO
-- :VAL2 : DNAME
-- :VAL3 : LOC

SELECT COUNT(*) INTO :CNT FROM DEPT WHERE DEPTNO = :VAL;

IF :CNT = 0 THEN
    INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (:VAL1, :VAL2, :VAL3)
ELSE
    UPDATE DEPT SET DNAME = :VAL2, LOC = :VAL3 WHERE DEPTNO = :VAL1

-- 아래와 같이 하면 SQL을 최대 두 번 수행합니다.

UPDATE DEPT SET DNAME = :VAL2, LOC = :VAL3 WHERE DEPTNO = :VAL1

IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (:VAL1, :VAL2, :VAL3)
END IF;

-- MERGE 문을 활용하면 SQL을 한 번만 수행합니다.
MERGE INTO DEPT A
USING (
    SELECT :VAL1 DEPTNO, :VAL2 DNAME, :VAL3 LOC FROM DUAL) B
)
ON B.DEPTNO = A.DEPTNO
WHEN MATCHED THEN
    UPDATE SET DNAME = B.NAME, LOC =  B.LOC
WHEN NOT MATCHED THEN
    INSERT (A.DEPTNO, A.DNAME, A.LOC) VALUES (B.DEPTNO, B.DNAME, B.LOC)

 

수정가능 조인뷰는 모두 MERGE 문으로 대체가능할까?

 

MERGE INTO EMP T2
USING (
    SELECT T.ROWID AS RID, S.ENAME
    FROM EMP T, EMP_SRC S
    WHERE T.EMPNO = S.EMPNO
    AND T.ENAME <> S.ENAME
) S
ON (T2.ROWID = S.RID)
WHEN MATCHED THEN UPDATE SET T2.ENAME = S.ENAME

 

위 Merge 문의 장점은 UPDATE의 대상을 쉽게 확인할 수 있다는 점입니다. select 문을 먼저 만들어서 UPDATE 대상에 대한 검증을 마친후 MERGE 문을 씌우는 패턴입니다. 위 패턴은 EMP 테이블을 두 번 읽는다는 점에서 매우 비효율적입니다.

 

MERGE INTO EMP T
USING EMP_SRC S
ON(T.EMPNO = S.EMPNO)
WHEN MATCHED THEN UPDATE SET T2.ENAME = S.ENAME
WHERE T.ENAME <> S.ENAME;

 

위와 같이 작성하는 것이 효율적입니다. 단, 복잡한 조인과 서브쿼리를 포함하는 경위 SELECT 문으로 검정할 필요가 있습니다. 차라리 update 문을 이용하면 데이터를 검증하면서 테이블도 한 번만 읽는 것이 가능합니다.

 

UPDATE(
  SELECT T.ROWID AS RID, S.ENAME
  FROM EMP T, EMP_SRC S
  WHERE T.EMPNO = S.EMPNO
  AND T.ENAME <> S.ENAME
)
SET T_ENAME = S_ENAME;