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 처리가 정상적으로 완료되었습니다.
참고 링크