BATCH NL 조인과 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션

BATCH NL 조인과 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션의 동작을 살펴보자.

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

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

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

CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c2);
CREATE INDEX t3_x1 ON t3 (c1);

아래 쿼리는 BATCH NL 방식으로 조인된다. t1 테이블이 TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 액세스된 반면, t2 테이블은 TABLE ACCESS BY INDEX ROWID 방식으로 액세스된다. 이미 BATCH NL 조인으로 수행되기 때문에 중복 동작을 배제한 것이다.

-- 2
SELECT *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c2 = a.c2;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  NESTED LOOPS                         |       |
|   2 |   NESTED LOOPS                        |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2    |
-------------------------------------------------------

아래 쿼리처럼 NO_NLJ_BATCHING 힌트로 PREFETCH NL 조인을 유도해보면 t2 테이블이 TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 액세스되는 것을 확인할 수 있다.

-- 3
SELECT /*+ NO_NLJ_BATCHING(B) */
       *
  FROM t1 a, t2 b
 WHERE a.c1 = 1
   AND b.c2 = a.c2;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED  | T2    |
|   2 |   NESTED LOOPS                        |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |
|*  5 |    INDEX RANGE SCAN                   | T2_X1 |
-------------------------------------------------------

아래 쿼리는 테이블 3개를 조인했다. 마지막 t3 테이블만 BATCH NL 방식으로 조인되는 것을 확인할 수 있다. t2 테이블은 일반 NL 조인 방식으로 조인되고, TABLE ACCESS BY INDEX ROWID BATCHED 방식으로 액세스되었다.

-- 4
SELECT *
  FROM t1 a, t2 b, t3 c
 WHERE a.c1 = 1
   AND b.c2 = a.c2
   AND c.c1 = b.c1;

--------------------------------------------------------
| Id  | Operation                              | Name  |
--------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |
|   1 |  NESTED LOOPS                          |       |
|   2 |   NESTED LOOPS                         |       |
|   3 |    NESTED LOOPS                        |       |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  5 |      INDEX RANGE SCAN                  | T1_X1 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2    |
|*  7 |      INDEX RANGE SCAN                  | T2_X1 |
|*  8 |    INDEX RANGE SCAN                    | T3_X1 |
|   9 |   TABLE ACCESS BY INDEX ROWID          | T3    |
--------------------------------------------------------

인라인 뷰를 사용하면 t2, t3 테이블 모두 BATCH NL 방식으로 조인되고, TABLE ACCESS BY INDEX ROWID 방식으로 액세스된다.

-- 5
SELECT /*+ NO_MERGE(A) */
       *
  FROM (SELECT a.c2, b.c1
          FROM t1 a, t2 b
         WHERE a.c1 = 1
           AND b.c2 = a.c2) a
     , t3 c
 WHERE c.c1 = a.c1;

----------------------------------------------------------
| Id  | Operation                                | Name  |
----------------------------------------------------------
|   0 | SELECT STATEMENT                         |       |
|   1 |  NESTED LOOPS                            |       |
|   2 |   NESTED LOOPS                           |       |
|   3 |    VIEW                                  |       |
|   4 |     NESTED LOOPS                         |       |
|   5 |      NESTED LOOPS                        |       |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  7 |        INDEX RANGE SCAN                  | T1_X1 |
|*  8 |       INDEX RANGE SCAN                   | T2_X1 |
|   9 |      TABLE ACCESS BY INDEX ROWID         | T2    |
|* 10 |    INDEX RANGE SCAN                      | T3_X1 |
|  11 |   TABLE ACCESS BY INDEX ROWID            | T3    |
----------------------------------------------------------