예전에 조건 기술에 따른 MERGE 문의 성능 차이라는 글을 썼습니다. MERGE 문은 조회한 칼럼에 따라서도 성능의 차이가 발생할 수 있습니다. 구체적으로는 MERGE 문이 해시 조인되는 경우 INTO 절과 USING 절에 필요한 칼럼만 조회함으로써 성능을 개선할 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, 1 AS c2, LPAD ('X', 4000, 'X') AS c3 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, 1 AS c2, LPAD ('X', 4000, 'X') AS c3 FROM XMLTABLE ('1 to 10000');
아래 MERGE 문은 t1, t2 테이블을 해시 조인합니다. 수행에 0.7초가 소요되고, 44 MB의 PGA를 사용합니다.
-- 2
MERGE
INTO t1 t
USING t2 s
ON (t.c1 = s.c1)
WHEN MATCHED THEN
UPDATE SET t.c2 = s.c2;
Plan hash value: 2683531971
-----------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 0 |00:00:00.70 | 30316 | 10001 | |
| 1 | MERGE | T1 | 0 |00:00:00.70 | 30316 | 10001 | |
| 2 | VIEW | | 10000 |00:00:00.56 | 20091 | 10000 | |
|* 3 | HASH JOIN | | 10000 |00:00:00.56 | 20091 | 10000 | 44M (0)| -- !
| 4 | TABLE ACCESS FULL| T2 | 10000 |00:00:00.40 | 10044 | 10000 | |
| 5 | TABLE ACCESS FULL| T1 | 10000 |00:00:00.03 | 10044 | 0 | |
-----------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[16336], SYSDEF[1], SYSDEF[120], SYSDEF[16336]
2 - "S"."C2"[NUMBER,22]
3 - (#keys=1) "S"."C1"[NUMBER,22], "T"."C1"[NUMBER,22], "S"."C3"[VARCHAR2,4000], "S"."C2"[NUMBER,22],
"T".ROWID[ROWID,10], "T"."C3"[VARCHAR2,4000], "T"."C2"[NUMBER,22]
4 - (rowset=15) "S"."C1"[NUMBER,22], "S"."C2"[NUMBER,22], "S"."C3"[VARCHAR2,4000] -- !
5 - (rowset=15) "T".ROWID[ROWID,10], "T"."C1"[NUMBER,22], "T"."C2"[NUMBER,22], "T"."C3"[VARCHAR2,4000]
아래 MERGE 문은 INTO 절과 USING 절에 c1, c2 칼럼을 조회한 인라인 뷰을 사용합니다. 수행에 0.39초가 소요되고 2108 KB의 PGA를 사용합니다. Column Projection Information 항목 중 4번에서 성능 차이의 원인을 찾을 수 있습니다. 앞선 MERGE 문은 c1, c2, c3 열을 해시 맵으로 빌드인하고, 아래 MERGE 문은 c1, c2 열만 해시 맵으로 빌드인합니다. 이로 인해 성능의 차이가 발생한 것입니다.
-- 3
MERGE
INTO (SELECT c1, c2 FROM t1) t
USING (SELECT c1, c2 FROM t2) s
ON (t.c1 = s.c1)
WHEN MATCHED THEN
UPDATE SET t.c2 = s.c2;
Plan hash value: 2683531971
-----------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 0 |00:00:00.39 | 30288 | 10000 | |
| 1 | MERGE | T1 | 0 |00:00:00.39 | 30288 | 10000 | |
| 2 | VIEW | | 10000 |00:00:00.27 | 20063 | 10000 | |
|* 3 | HASH JOIN | | 10000 |00:00:00.27 | 20063 | 10000 | 2107K (0)| -- !
| 4 | TABLE ACCESS FULL| T2 | 10000 |00:00:00.22 | 10016 | 10000 | |
| 5 | TABLE ACCESS FULL| T1 | 10000 |00:00:00.03 | 10044 | 0 | |
-----------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[16336], SYSDEF[1], SYSDEF[120], SYSDEF[16336]
2 - "S"."C2"[NUMBER,22]
3 - (#keys=1) "C1"[NUMBER,22], "C1"[NUMBER,22], "C2"[NUMBER,22], "T".ROWID[ROWID,10], "C2"[NUMBER,22]
4 - (rowset=256) "C1"[NUMBER,22], "C2"[NUMBER,22] -- !
5 - (rowset=256) "T".ROWID[ROWID,10], "C1"[NUMBER,22], "C2"[NUMBER,22]
[2023-05-14]
MERGE 문에 대한 Column Projection이 제대로 동작하지 않는 동작은 23.2 버전까지 개선되지 않은 것으로 보입니다.