인덱스 유형에 따른 Buffer Pinning

Buffer Pinning은 인덱스 유형에 따른 동작의 차이가 있다. 이번 글에서 관련 내용을 살펴보자.

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

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

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

CREATE UNIQUE INDEX t2_u1 ON t2 (c1);
CREATE INDEX t2_x1 ON t2 (c2);

t2_u1 인덱스는 UNIQUE 인덱스, t2_x1 인덱스는 NONUNIQUE 인덱스다. 물리적으로 큰 차이가 없는 것을 확인할 수 있다.

-- 1-2
SELECT index_name, uniqueness, blevel, leaf_blocks, clustering_factor
  FROM user_indexes
 WHERE index_name IN ('T2_U1', 'T2_X1');

INDEX_NAME UNIQUENES BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- --------- ------ ----------- -----------------
T2_U1      UNIQUE         1          20                18
T2_X1      NONUNIQUE      1          21                18

2 행이 선택되었습니다.

아래는 t2_x1 인덱스를 사용했다. 4번 INDEX RANGE SCAN, 5번 TABLE ACCESS BY INDEX ROWID 단계 모두 Buffer Pinning이 동작한다. 각각 207, 101개의 블록 I/O가 발생한 것을 확인할 수 있다.

-- 2
SELECT /*+ LEADING(A) USE_NL(B) INDEX(B T2_X1) */
       *
  FROM t1 a, t2 b
 WHERE b.c2 = a.c2;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |  10000 |     428 |
|   1 |  NESTED LOOPS                |       |      1 |  10000 |     428 |
|   2 |   NESTED LOOPS               |       |      1 |  10000 |     327 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |  10000 |     120 |
|*  4 |    INDEX RANGE SCAN          | T2_X1 |  10000 |  10000 |     207 | -- !
|   5 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |  10000 |     101 | -- !
--------------------------------------------------------------------------

아래 쿼리는 t2_u1 인덱스를 사용했다. 4번 INDEX UNIQUE SCAN 단계는 Buffer Pinning에 의해 108개의 블록 I/O가 발생했지만, 5번 TABLE ACCESS BY INDEX ROWID 단계는 Buffer Pinning이 동작하지 않아 10,000개의 블록 I/O가 발생했다.

-- 3
SET ARRAYSIZE 100

SELECT /*+ LEADING(A) USE_NL(B) INDEX(B T2_U1) */
       *
  FROM t1 a, t2 b
 WHERE b.c1 = a.c2;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |  10000 |   10228 |
|   1 |  NESTED LOOPS                |       |      1 |  10000 |   10228 |
|   2 |   NESTED LOOPS               |       |      1 |  10000 |     228 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |  10000 |     120 |
|*  4 |    INDEX UNIQUE SCAN         | T2_U1 |  10000 |  10000 |     108 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |  10000 |   10000 | -- !
--------------------------------------------------------------------------

이러한 UNIQUE 인덱스의 동작은 테이블 랜덤 액세스 단계에서 과도한 블록 I/O를 발생시킬 수 있다. Buffer Pinning 효과가 큰 경우라면 아래와 같이 INDEX RANGE SCAN을 유도하여 블록 I/O 발생을 감소시키는 방식을 고려할 수 있다.

-- 4
SELECT /*+ LEADING(A) USE_NL(B) INDEX(B T2_U1) */
       *
  FROM t1 a, t2 b
 WHERE b.c1 BETWEEN a.c2 AND a.c2 + 0;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |  10000 |     428 |
|   1 |  NESTED LOOPS                |       |      1 |  10000 |     428 |
|   2 |   NESTED LOOPS               |       |      1 |  10000 |     327 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |  10000 |     120 |
|*  4 |    INDEX RANGE SCAN          | T2_U1 |  10000 |  10000 |     207 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |  10000 |     101 |
--------------------------------------------------------------------------