소트 머지 조인의 조인 조건에 따른 수행 방식 차이

소트 머지 조인은 조인 조건에 따라 수행 방식에 차이가 있다.

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

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

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

CREATE INDEX t1_x1 ON t1 (c1);

아래 쿼리는 t1, t2의 조인 조건과 t2, t3의 조인 조건이 c1으로 동일하다. 먼저 t2, t3를 소트 영역에 저장하고, t1을 스캔하며 t2, t3를 소트 머지 조인하는 방식으로 수행된다. t2, t3 조인 과정에서 부분 범위 처리가 동작한 것을 확인할 수 있다. (6번, 8번 오퍼레이션의 Starts와 A-Rows가 100)

-- 2
SELECT /*+ LEADING(A B C) USE_MERGE(A B C) INDEX(A) */
       *
  FROM t1 a, t2 b, t3 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c1
   AND ROWNUM <= 100;

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |    100 |00:00:00.52 |    3260 |          |
|*  1 |  COUNT STOPKEY                 |       |      1 |    100 |00:00:00.52 |    3260 |          |
|   2 |   MERGE JOIN                   |       |      1 |    100 |00:00:00.51 |    3260 |          | -- (09)
|   3 |    MERGE JOIN                  |       |      1 |    100 |00:00:00.07 |    1633 |          | -- (07)
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100 |00:00:00.01 |       6 |          | -- (06)
|   5 |      INDEX FULL SCAN           | T1_X1 |      1 |    100 |00:00:00.01 |       3 |          | -- (05)
|*  6 |     SORT JOIN                  |       |    100 |    100 |00:00:00.07 |    1627 |   12M (0)| -- (02), (08)
|   7 |      TABLE ACCESS FULL         | T2    |      1 |    100K|00:00:00.02 |    1627 |          | -- (01)
|*  8 |    SORT JOIN                   |       |    100 |    100 |00:00:00.44 |    1627 |   12M (0)| -- (04), (10)
|   9 |     TABLE ACCESS FULL          | T3    |      1 |    100K|00:00:00.03 |    1627 |          | -- (03)
----------------------------------------------------------------------------------------------------

아래 쿼리는 t1, t2의 조인 조건과 t2, t3의 조인 조건이 상이하다. 먼저 t2를 소트 영역에 저장하고, t1을 스캔하며 t2를 소트 머지 조인하고 조인 결과를 소트 영역에 저장한 후, 이어서 t3를 소트 영역에 저장하고, 조인 결과를 스캔하며 t3를 소트 머지 조인하는 방식으로 수행된다. t3 조인 과정에서만 부분 범위 처리가 동작한다. (7번 오퍼레이션의 Starts와 A-Rows가 100K)

-- 3
SELECT /*+ LEADING(A B C) USE_MERGE(A B C) INDEX(A) */
       *
  FROM t1 a, t2 b, t3 c
 WHERE b.c1 = a.c1
   AND c.c1 = b.c2
   AND ROWNUM <= 100;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |      1 |    100 |00:00:01.02 |    5088 |          |
|*  1 |  COUNT STOPKEY                  |       |      1 |    100 |00:00:01.02 |    5088 |          |
|   2 |   MERGE JOIN                    |       |      1 |    100 |00:00:00.99 |    5088 |          | -- (10)
|   3 |    SORT JOIN                    |       |      1 |    100 |00:00:00.83 |    3461 |   23M (0)| -- (07)
|   4 |     MERGE JOIN                  |       |      1 |    100K|00:00:00.36 |    3461 |          | -- (05)
|   5 |      TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100K|00:00:00.12 |    1834 |          | -- (04)
|   6 |       INDEX FULL SCAN           | T1_X1 |      1 |    100K|00:00:00.04 |     223 |          | -- (03)
|*  7 |      SORT JOIN                  |       |    100K|    100K|00:00:00.19 |    1627 |   12M (0)| -- (02), (06)
|   8 |       TABLE ACCESS FULL         | T2    |      1 |    100K|00:00:00.02 |    1627 |          | -- (01)
|*  9 |    SORT JOIN                    |       |    100 |    100 |00:00:00.16 |    1627 |   12M (0)| -- (09), (11)
|  10 |     TABLE ACCESS FULL           | T3    |      1 |    100K|00:00:00.02 |    1627 |          | -- (08)
-----------------------------------------------------------------------------------------------------