IOT Secondary 인덱스

IOT(Indexed Organized Table)의 Secondary 인덱스는 일반 인덱스와 동작 방식이 상이하다. 관련 내용을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1, c2, c3, CONSTRAINT t1_pk PRIMARY KEY (c1)) ORGANIZATION INDEX AS
SELECT ROWNUM AS c1, ROWNUM AS c2, 1 AS c3 FROM DUAL CONNECT BY LEVEL <= 10000;

CREATE INDEX t1_x1 ON t1 (c2);

아래 쿼리는 t1_x1 인덱스만 읽고 결과를 반환했다. t1_x1 인덱스에 t1 테이블의 PK 값(c1)이 저장되어 있기 때문이다. IOT는 B-tree 구조로 저장되기 때문에 블록 스플릿(split)에 의한 ROWID 변경이 발생할 수 있다. ROWID가 변경되면 인덱스에 저장된 ROWID로 IOT를 액세스할 수 없기 때문에 PK 값을 함께 보관하는 것이다.

-- 2
SELECT c1 FROM t1 WHERE c2 <= 100;

--------------------------------------------------------------
| Id  | Operation        | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------
|   0 | SELECT STATEMENT |       |      1 |    100 |       9 |
|*  1 |  INDEX RANGE SCAN| T1_X1 |      1 |    100 |       9 |
--------------------------------------------------------------

*_INDEXES 뷰의 pct_direct_access 열은 Direct Access 비율(Percent = PCT)을 나타낸다. t1_x1 인덱스는 생성 후 데이터 변경이 발생하기 않았기 때문에 pct_direct_access 값이 100이다. pct_direct_access 값이 100인 경우에는 ROWID로 IOT(t1_pk)를 액세스하고, 인덱스에 저장된 PK 값과 액세스한 행의 PK 값을 비교한 후 값이 일치하면 결과를 반환한다. 한가지 흥미로운 점은 이런 동작으로 인해 Buffer Pinning이 동작하지 않는다는 것이다.(t1_x1 인덱스의 A-Rows가 100, t1_pk IOT에서 발생한 Buffers도 100이다.)

-- 3-1
SELECT pct_direct_access FROM user_indexes WHERE index_name = 'T1_X1';

PCT_DIRECT_ACCESS
-----------------
              100

1개의 행이 선택되었습니다.

-- 3-2
SELECT * FROM t1 WHERE c2 <= 100;

---------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |    100 |     109 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK |      1 |    100 |     109 |
|*  2 |   INDEX RANGE SCAN| T1_X1 |      1 |    100 |       9 |
---------------------------------------------------------------

IOT에 신규 데이터를 입력해보자. 인덱스 통계 정보를 수집하지 않았기 때문에 pct_direct_access 값은 여전히 100이다. 인덱스의 PK 값과 액세스한 행의 PK 값이 서로 일치하지 않으면 ROWID가 변경된 것이므로 PK 값으로 IOT를 다시 액세스한다. 이로 인해 아래 쿼리는 102개(=319-17-200)의 추가적인 블록 I/O가 발생했다.

-- 4-1
INSERT INTO t1 SELECT ROWNUM + 0.5 AS c1, ROWNUM  AS c2, 1 AS c3 FROM DUAL CONNECT BY LEVEL <= 10000;
COMMIT;

-- 4-2
SELECT pct_direct_access FROM user_indexes WHERE index_name = 'T1_X1';

PCT_DIRECT_ACCESS
-----------------
              100

1개의 행이 선택되었습니다.

-- 4-3
SELECT * FROM t1 WHERE c2 <= 100;

---------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |    200 |     319 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK |      1 |    200 |     319 |
|*  2 |   INDEX RANGE SCAN| T1_X1 |      1 |    200 |      17 |
---------------------------------------------------------------

t1_x2 인덱스의 통계 정보를 수집해보자. pct_direct_access 값이 64로 변경된 것을 확인할 수 있다. pct_direct_access 값이 100 미만인 경우에는 PK 값으로만 IOT를 액세스한다. 쿼리를 다시 수행해보면 블록 I/O가 54로 감소한 것을 확인할 수 있다. IOT 액세스 단계에서 34개(=54-17)의 블록 I/O만 발생했다. Buffer Pinning이 동작하는 것을 유추할 수 있다.

-- 5-1
EXEC DBMS_STATS.GATHER_INDEX_STATS (ownname => 'TUNA', indname => 'T1_X1');

-- 5-2
SELECT pct_direct_access FROM user_indexes WHERE index_name = 'T1_X1';

PCT_DIRECT_ACCESS
-----------------
               64

1개의 행이 선택되었습니다.

-- 5-3
SELECT * FROM t1 WHERE c2 <= 100;

---------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |    200 |      54 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK |      1 |    200 |      54 |
|*  2 |   INDEX RANGE SCAN| T1_X1 |      1 |    200 |      17 |
---------------------------------------------------------------

t1_x1 인덱스를 REBUILD해보자.^[각주:1] pct_direct_access 값이 100으로 변경된 것을 확인할 수 있다. 인덱스를 REBUILD하기 전보다 블록 I/O가 증가한 것을 확인할 수 있다. CF가 좋은 인덱스임에도 Buffer Pinning이 동작하지 않아 발생한 현상이다.

-- 6-1
ALTER INDEX t1_x1 REBUILD;
-- ALTER INDEX t1_x1 UPDATE BLOCK REFERENCES;

-- 6-2
SELECT pct_direct_access, clustering_factor, num_rows FROM user_indexes WHERE index_name = 'T1_X1';

PCT_DIRECT_ACCESS CLUSTERING_FACTOR NUM_ROWS
----------------- ----------------- --------
              100               101    20000

1개의 행이 선택되었습니다.

-- 6-3
SELECT * FROM t1 WHERE c2 <= 100;

---------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |    200 |     216 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK |      1 |    200 |     216 |
|*  2 |   INDEX RANGE SCAN| T1_X1 |      1 |    200 |      16 |
---------------------------------------------------------------

pct_direct_access 값을 99로 설정하면 Buffer Pinning 효과로 인해 블록 I/O가 감소하는 것을 확인할 수 있다. 그렇다고 의도적으로 CF가 좋은 인덱스의 pct_direct_access 값을 100 미만으로 설정하는 것은 바람직하지 않다. t1_x1 인덱스는 특별한 케이스다. 일반적인 Secondary 인덱스는 CF가 좋지 않고 Depth가 깊기 때문에 성능이 저하되는 것이 일반적이다.

-- 7-1
EXEC DBMS_STATS.SET_INDEX_STATS (ownname => 'TUNA', indname => 'T1_X1', guessq => 99);

-- 7-2
SELECT pct_direct_access FROM user_indexes WHERE index_name = 'T1_X1';

PCT_DIRECT_ACCESS
-----------------
               99

1개의 행이 선택되었습니다.

-- 7-3
SELECT * FROM t1 WHERE c2 <= 100;

---------------------------------------------------------------
| Id  | Operation         | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |    200 |      53 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK |      1 |    200 |      53 |
|*  2 |   INDEX RANGE SCAN| T1_X1 |      1 |    200 |      16 |
---------------------------------------------------------------

기본적으로 Secondary 인덱스가 불필요한 테이블을 IOT 적용 대상으로 고려한다. 가능한 Secondary 인덱스를 생성하지 않는 편이 바람직하며, 생성한 경우에는 통계 정보 수집과 인덱스 REBUILD를 통해 성능 저하를 방지해야 한다.^[각주:2]

1. UPDATE BLOCK REFERENCES 절을 사용해도 된다. [본문으로]
2. 관련 내용은 오라클 성능 고도화 원리와 해법 2권의 113~116 페이지를 참조하자. [본문으로]