조인 방식에 따른 부분 범위 처리

조인 방식에 따른 부분 범위 처리에 대한 내용을 살펴보자.

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

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

CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10');
CREATE TABLE t2 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10');

NL 조인은 부분 범위 처리 가능하다.

-- 2
SELECT /*+ ORDERED USE_NL(B) */
       *
  FROM t1 a
     , t2 b
 WHERE b.c1 = a.c1
   AND ROWNUM <= 1;

------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows |
------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |      1 |
|*  1 |  COUNT STOPKEY      |      |      1 |      1 |
|   2 |   NESTED LOOPS      |      |      1 |      1 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |      1 |
|*  4 |    TABLE ACCESS FULL| T2   |      1 |      1 |
------------------------------------------------------

해시 조인은 아우터 테이블(t2)만 부분 범위 처리가 가능하다.

-- 3
SELECT /*+ ORDERED USE_HASH(B) */
       *
  FROM t1 a
     , t2 b
 WHERE b.c1 = a.c1
   AND ROWNUM <= 1;
-----------------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows | Used-Mem |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |      1 |          |
|*  1 |  COUNT STOPKEY      |      |      1 |      1 |          |
|*  2 |   HASH JOIN         |      |      1 |      1 | 1258K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |      1 |          |
-----------------------------------------------------------------

소트 머지 조인은 아우터 테이블(t1)에 인덱스가 존재하는 경우(4-2) 아우터 테이블만 부분 범위 처리가 가능하다.

-- 4-1
SELECT /*+ ORDERED USE_MERGE(B) */
       *
  FROM t1 a
     , t2 b
 WHERE b.c1 = a.c1
   AND ROWNUM <= 1;

------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows | Used-Mem |
------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |      1 |          |
|*  1 |  COUNT STOPKEY       |      |      1 |      1 |          |
|   2 |   MERGE JOIN         |      |      1 |      1 |          |
|   3 |    SORT JOIN         |      |      1 |      1 | 2048  (0)|
|   4 |     TABLE ACCESS FULL| T1   |      1 |     10 |          |
|*  5 |    SORT JOIN         |      |      1 |      1 | 2048  (0)|
|   6 |     TABLE ACCESS FULL| T2   |      1 |     10 |          |
------------------------------------------------------------------

-- 4-2
CREATE INDEX t1_x1 ON t1 (c1);

SELECT /*+ ORDERED USE_MERGE(B) */
       *
  FROM t1 a
     , t2 b
 WHERE b.c1 = a.c1
   AND ROWNUM <= 1;

-------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | A-Rows | Used-Mem |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |      1 |          |
|*  1 |  COUNT STOPKEY       |       |      1 |      1 |          |
|   2 |   MERGE JOIN         |       |      1 |      1 |          |
|   3 |    INDEX FULL SCAN   | T1_X1 |      1 |      1 |          |
|*  4 |    SORT JOIN         |       |      1 |      1 | 2048  (0)|
|   5 |     TABLE ACCESS FULL| T2    |      1 |     10 |          |
-------------------------------------------------------------------