MERGE 문의 개별 Insert, Update, Delete 로우 수

*_TAB_MODIFICATIONS 뷰의 변경 정보로 MERGE 문의 개별 Insert, Update, Delete 로우 수를 계산할 수 있습니다. 다만 지난 글의 세션 통계와 달리 변경 정보는 시스템 수준에서 저장되므로 이 방법은 ETL처럼 단독으로 수행되는 MERGE 문에만 사용할 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
 
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, 1 FROM XMLTABLE ('1 to 200');
CREATE TABLE t2 (c1, c2) AS SELECT ROWNUM, CEIL (ROWNUM / 100) FROM XMLTABLE ('1 to 500');

 

아래와 같이 *_TAB_MODIFICATIONS 뷰의 변경 정보로 MERGE 문의 개별 Insert, Update, Delete 로우 수를 계산할 수 있습니다. 참고로 MERGE 문의 경우 updates 칼럼에 실제 Update 로우 수에서 Delete 로우 수를 뺀 값이 저장됩니다.

-- 2
SET SERVEROUT ON
 
DECLARE
    v_inserts_bf NUMBER;
    v_updates_bf NUMBER;
    v_deletes_bf NUMBER;
    v_inserts_af NUMBER;
    v_updates_af NUMBER;
    v_deletes_af NUMBER;
 
    PROCEDURE p1 (
        o_inserts OUT NUMBER
      , o_updates OUT NUMBER
      , o_deletes OUT NUMBER
    )
    IS
    BEGIN
        SELECT NVL (SUM (inserts), 0) AS inserts
             , NVL (SUM (updates), 0) AS updates
             , NVL (SUM (deletes), 0) AS deletes
          INTO o_inserts
             , o_updates
             , o_deletes
          FROM user_tab_modifications
         WHERE table_name = 'T1';
    END;
BEGIN
    p1 (v_inserts_bf, v_updates_bf, v_deletes_bf);
 
    MERGE
     INTO t1 t
    USING t2 s
       ON (t.c1 = s.c1)
     WHEN MATCHED THEN
          UPDATE SET t.c2 = s.c2
          DELETE WHERE t.c2 = 1
     WHEN NOT MATCHED THEN
          INSERT (t.c1, t.c2) VALUES (s.c1, s.c1);

    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    p1 (v_inserts_af, v_updates_af, v_deletes_af);

    DBMS_OUTPUT.PUT_LINE ('INSERTS = ' ||  (v_inserts_af - v_inserts_bf));
    DBMS_OUTPUT.PUT_LINE ('UPDATES = ' || ((v_updates_af - v_updates_bf) + (v_deletes_af - v_deletes_bf)));
    DBMS_OUTPUT.PUT_LINE ('DELETES = ' ||  (v_deletes_af - v_deletes_bf));
    ROLLBACK;
END;
/
INSERTS = 300
UPDATES = 200
DELETES = 100

PL/SQL 처리가 정상적으로 완료되었습니다.