Oracle Database는 MERGE 문의 개별 Insert, Update 로우 수를 제공하지 않습니다. 패키지 변수로 MERGE 문의 개별 Insert, Update 로우 수를 계산할 수 있지만 성능 저하가 발생할 수 있습니다.

 

아래 세션 통계를 사용하면 성능 저하없이 MERGE 문의 개별 Insert, Update 로우 수를 계산할 수 있습니다. 참고로 MERGE 문의 Update과 Delete는 Row 단위, Insert는 Array 단위로 처리됩니다.

통계 내용
HSC Heap Segment Block Changes  힙 세그먼트의 블록을 변경하기 위해 액세스한 로우 수 (Array 처리는 1 증가)
Heap Segment Array Inserts 힙 세그먼트에 대한 Array Insert 횟수

 

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

-- 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');

 

아래와 같이 ROWCOUNT 속성 값과 HSC Heap Segment Block Changes, Heap Segment Array Inserts 통계 값으로 MERGE 문의 개별 Insert, Update 로우 수를 계산할 수 있습니다.

-- 2
SET SERVEROUT ON
 
DECLARE
    v_rows NUMBER;
    v_hsbc NUMBER;
    v_hsai NUMBER;
 
    FUNCTION f1 (i_name IN VARCHAR2)
        RETURN NUMBER
    IS
        v_value NUMBER;
    BEGIN
        SELECT b.value
          INTO v_value
          FROM v$statname a
             , v$mystat b
         WHERE a.name = i_name
           AND b.statistic# = a.statistic#;
 
        RETURN v_value;
    END;
BEGIN
    v_hsbc := f1 ('HSC Heap Segment Block Changes');
    v_hsai := f1 ('Heap Segment Array Inserts');
 
    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);
 
    v_rows := SQL%ROWCOUNT;
    v_hsbc := f1 ('HSC Heap Segment Block Changes') - v_hsbc;
    v_hsai := f1 ('Heap Segment Array Inserts') - v_hsai;
 
    DBMS_OUTPUT.PUT_LINE ('v_rows = ' || v_rows);
    DBMS_OUTPUT.PUT_LINE ('v_hsbc = ' || v_hsbc);
    DBMS_OUTPUT.PUT_LINE ('v_hsai = ' || v_hsai);
    DBMS_OUTPUT.PUT_LINE ('MERGE  = ' || (v_rows));
    DBMS_OUTPUT.PUT_LINE ('INSERT = ' || (v_rows - (v_hsbc - v_hsai)));
    DBMS_OUTPUT.PUT_LINE ('UPDATE = ' || (v_hsbc - v_hsai));
 
    ROLLBACK;
END;
/
v_rows = 500
v_hsbc = 202
v_hsai = 2
MERGE  = 500
INSERT = 300
UPDATE = 200
 
PL/SQL 처리가 정상적으로 완료되었습니다.

 

참고로 DELETE 절의 주석을 제거하고 앞선 코드를 다시 수행하면 HSC Heap Segment Block Changes 통계 값에 Delete 대상 로우 수가 더해져 Insert, Update의 로우 수가 잘못 계산됩니다.

-- 3
/
v_rows = 500
v_hsbc = 402
v_hsai = 2
MERGE  = 500
INSERT = 100
UPDATE = 400
 
PL/SQL 처리가 정상적으로 완료되었습니다.

 

참고 링크

저작자표시 비영리 변경금지

티스토리툴바