조건 기술에 따른 MERGE 문의 성능 차이

MERGE 문은 조건의 기술 위치에 따라 쿼리 성능에 차이가 발생할 수 있다.

 

테스트를 위해 아래와 같이 데이터를 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2, LPAD ('X', 4000, 'X') AS c3 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;

CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);

 

아래 쿼리는 MERGE UPDATE 절의 WHERE 절에 t1, t2 테이블에 대한 조건을 기술했다. Predicate Information에서 조건이 조인에 사용되지 못한 것을 확인할 수 있다.

-- 2
MERGE
 INTO t1 a
USING t2 b
   ON (b.c1 = a.c1)
 WHEN MATCHED THEN
    UPDATE
       SET a.c2 = b.c2
     WHERE a.c1 BETWEEN 1 AND 100
       AND b.c2 BETWEEN 1 AND 10;
 
-----------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |      1 |      0 |   20073 |
|   1 |  MERGE               | T1   |      1 |      0 |   20073 |
|   2 |   VIEW               |      |      1 |  10000 |   20063 |
|*  3 |    HASH JOIN         |      |      1 |  10000 |   20063 |
|   4 |     TABLE ACCESS FULL| T2   |      1 |  10000 |   10016 |
|   5 |     TABLE ACCESS FULL| T1   |      1 |  10000 |   10044 |
-----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."C1"="A"."C1")

 

t1 테이블의 조건을 인라인 뷰에 기술해보자. Predicate Information에서 조건이 조인에 사용된 것을 확인할 수 있다. Transitive Predicates에 의해 t2 테이블도 인덱스를 사용했다.

-- 3
MERGE
 INTO (SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 100) a
USING t2 b
   ON (b.c1 = a.c1)
 WHEN MATCHED THEN
    UPDATE
       SET a.c2 = b.c2
     WHERE b.c2 BETWEEN 1 AND 10;
 
------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                        |       |      1 |      0 |     214 |
|   1 |  MERGE                                 | T1    |      1 |      0 |     214 |
|   2 |   VIEW                                 |       |      1 |    100 |     204 |
|*  3 |    HASH JOIN                           |       |      1 |    100 |     204 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |    100 |     102 |
|*  5 |      INDEX RANGE SCAN                  | T2_X1 |      1 |    100 |       2 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |    100 |     102 |
|*  7 |      INDEX RANGE SCAN                  | T1_X1 |      1 |    100 |       2 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."C1"="T1"."C1")
   5 - access("B"."C1">=1 AND "B"."C1"<=100)
   7 - access("C1">=1 AND "C1"<=100)

 

t2 테이블의 조건까지 인라인 뷰에 기술해보자. t2 테이블의 예상 카디널리티가 줄어들면서 NL 조인으로 수행된 것을 확인할 수 있다. 이로 인해 블록 I/O도 조금 감소했다.

-- 4
MERGE
 INTO (SELECT * FROM t1 WHERE c1 BETWEEN 1 AND 100) a
USING (SELECT * FROM t2 WHERE c1 BETWEEN 1 AND 100 AND c2 BETWEEN 1 AND 10) b
   ON (b.c1 = a.c1)
 WHEN MATCHED THEN
    UPDATE
       SET a.c2 = b.c2;
 
-------------------------------------------------------------------------------------
| Id  | Operation                               | Name  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         |       |      1 |      0 |     132 |
|   1 |  MERGE                                  | T1    |      1 |      0 |     132 |
|   2 |   VIEW                                  |       |      1 |     10 |     120 |
|   3 |    NESTED LOOPS                         |       |      1 |     10 |     120 |
|   4 |     NESTED LOOPS                        |       |      1 |     10 |     110 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |     10 |     102 |
|*  6 |       INDEX RANGE SCAN                  | T2_X1 |      1 |    100 |       2 |
|*  7 |      INDEX RANGE SCAN                   | T1_X1 |     10 |     10 |       8 |
|   8 |     TABLE ACCESS BY INDEX ROWID         | T1    |     10 |     10 |      10 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("C2"<=10 AND "C2">=1))
   6 - access("C1">=1 AND "C1"<=100)
   7 - access("T2"."C1"="T1"."C1")
       filter(("C1"<=100 AND "C1">=1))

 

MERGE UPDATE 절이나 MERGE INSERT 절의 WHERE 절의 조건은 인라인 뷰에 기술하는 것이 성능적인 측면에서 바람직하다. 다만 MERGE UPDATE 절과 MERGE INSERT 절을 모두 사용하는 경우 결과가 달라지지 않게 주의해야 한다.