INLIST ITERATOR 성능 개선

INLIST ITERATOR 오퍼레이션은 인덱스 수직 탐색으로 인해 과도한 블록 I/O를 발생할 수 있다.

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

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

아래 쿼리는 IN 절로 10개의 행을 조회했다. 실행 계획 2번 Starts에서 t1_x1 인덱스가 10번 액세스된 것을 확인할 수 있다. 이로 인해 14개의 블록 I/O가 발생했다.^[각주:1]

-- 2
SELECT *
  FROM t1
 WHERE c1 IN (2, 4, 6, 8, 10, 12, 14, 16, 18, 20);

---------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |     10 |      14 |
|   1 |  INLIST ITERATOR  |       |      1 |     10 |      14 |
|*  2 |   INDEX RANGE SCAN| T1_X1 |     10 |     10 |      14 | -- !
---------------------------------------------------------------

조회 범위가 넓지 않다면 아래와 같이 쿼리를 변경하여 쿼리의 성능을 개선할 수 있다.

-- 3
SELECT *
  FROM t1
 WHERE c1 BETWEEN 2 AND 20
   AND c1 + 0 IN (2, 4, 6, 8, 10, 12, 14, 16, 18, 20);

--------------------------------------------------------------
| Id  | Operation        | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------
|   0 | SELECT STATEMENT |       |      1 |     10 |       4 |
|*  1 |  INDEX RANGE SCAN| T1_X1 |      1 |     10 |       4 | -- !
--------------------------------------------------------------

1. 인덱스 루트 블록과 브랜치 브록에 의한 Buffer Pinning으로 인해 블록 I/O가 감소한 것이다. [본문으로]