TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 #1

12.1 버전부터 INDEX RANGE SCAN으로 인덱스를 스캔하면 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션으로 테이블을 액세할 수 있다. 해당 오퍼레이션은 아래 순서로 동작하는 것으로 보인다.^[각주:1]

1. 리프 블록의 ROWID로 버퍼 캐시를 조회하고(single block I/O) 실패(miss)한 ROWID를 저장
2. 실패한 ROWID가 일정량 이상 모이면 블록 번호로 정렬하여 물리 읽기를 수행(multiblock I/O)

2번 과정으로 인해 물리 읽기가 발생한 경우 인덱스 정렬 순서와 무관하게 행이 반환될 수 있다. 관련 내용을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT MOD (ROWNUM, 100) AS c1, LPAD ('X', 1000, 'X') AS c2 FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1);

아래에서 2-1번 쿼리의 실행 계획에서 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션을 확인할 수 있다. 2-2번 쿼리는 ORDER BY 절을 기술했기 때문에 TABLE ACCESS BY INDEX ROWID 오퍼레이션으로 테이블을 액세스한다. BATCH_TABLE_ACCESS_BY_ROWID 힌트를 통해 해당 오퍼레이션을 제어할 수 있다. 2-3번 쿼리는 인덱스 순서로 정렬했음에도 불구하고 테이블을 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션으로 액세스했기 때문에 소트가 발생했다.

-- 2-1
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 > 0;

-----------------------------------------------------
| Id  | Operation                           | Name  |
-----------------------------------------------------
|   0 | SELECT STATEMENT                    |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |
-----------------------------------------------------

-- 2-2
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 > 0 ORDER BY c1;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

-- 2-3
SELECT /*+ INDEX(T1) BATCH_TABLE_ACCESS_BY_ROWID(T1) */ * FROM t1 WHERE c1 > 0 ORDER BY c1;

------------------------------------------------------
| Id  | Operation                            | Name  |
------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |
|   1 |  SORT ORDER BY                       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  3 |    INDEX RANGE SCAN                  | T1_X1 |
------------------------------------------------------

실제로 인덱스 정렬 순서와 무관하게 행이 반환되는지 테스트를 통해 살펴보자. 3-1번 쿼리는 TABLE ACCESS BY INDEX ROWID, 3-2번 쿼리는 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션으로 테이블을 액세스했다. 버퍼 캐시를 비우고, 조회 결과를 테이블에 저장해보자.^[각주:2]

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

ALTER SYSTEM FLUSH BUFFER_CACHE;

CREATE TABLE t2 AS
SELECT /*+ INDEX(A) NO_BATCH_TABLE_ACCESS_BY_ROWID(A) */ ROWNUM AS rn, a.* FROM t1 a WHERE a.c1 >= 0;

-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | CREATE TABLE STATEMENT        |       |
|   1 |  LOAD AS SELECT               | T2    |
|   2 |   COUNT                       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |
|*  4 |     INDEX RANGE SCAN          | T1_X1 |
-----------------------------------------------

-- 3-2
ALTER SYSTEM FLUSH BUFFER_CACHE;

CREATE TABLE t3 AS
SELECT /*+ INDEX(A) BATCH_TABLE_ACCESS_BY_ROWID(A) */ ROWNUM AS rn, a.* FROM t1 a WHERE a.c1 >= 0;

-------------------------------------------------------
| Id  | Operation                             | Name  |
-------------------------------------------------------
|   0 | CREATE TABLE STATEMENT                |       |
|   1 |  LOAD AS SELECT                       | T3    |
|   2 |   COUNT                               |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  4 |     INDEX RANGE SCAN                  | T1_X1 |
-------------------------------------------------------

t2 테이블은 인덱스 정렬 순서대로 데이터가 입력된 반면, t3 테이블은 인덱스 정렬  순서와 무관하게 데이터가 입력된 것을 확인할 수 있다. 

-- 4-1
SELECT   rn, c1
    FROM t2
   WHERE rn BETWEEN 96 AND 105
ORDER BY rn;

 RN C1
--- --
 96  0
 97  0
 98  0
 99  0
100  0
101  1
102  1
103  1
104  1
105  1

10 행이 선택되었습니다.

-- 4-3
SELECT   rn, c1
    FROM t3
   WHERE rn BETWEEN 96 AND 105
ORDER BY rn;

 RN C1
--- --
 96  1
 97  0
 98  1
 99  2
100  0
101  1
102  2
103  0
104  1
105  2

10 행이 선택되었습니다.

1. NLJ Batching와 유사하게 동작한다. [본문으로]
2. 행이 많기 때문에 테이블에 저장한 것이다. fetch를 멈출 수 있는 서드 파티 툴을 사용하면 결과를 바로 확인할 수 있다. [본문으로]