Covered 인덱스와 인덱스 브랜치 블록

테이블 랜덤 액세스를 제거하기 위해 인덱스 뒤쪽에 액세스할 칼럼을 추가한 인덱스를 Covered 인덱스라고 한다. 칼럼 추가로 인해 브랜치 블록의 크기가 커지기 때문에 SQL Server는 INCLUDE 절을 제공하고 있다.^[각주:1] 아쉽게 오라클 데이터베이스는 해당 기능이 없다. Covered 인덱스 생성시 고려해야 할 사항을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자. c1 열은 고유 값을 가진다. c2 열은 인덱스 선두 칼럼이다. c3 열과 c4 열은 인덱스 뒤쪽에 추가할 칼럼으로 c3 열은 동일 값, c4 열은 임의 값을 가진다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1
     , CEIL (ROWNUM / 10) AS c2
     , LPAD ('X', 10, 'X') AS c3
     , LPAD (ORA_HASH (ROWNUM), 10, '0') AS c4
  FROM XMLTABLE ('1 to 1000000');

아래와 같이 인덱스를 생성하자. t1_x1 인덱스는 동일 값을 가진 c3 열, t2_x2 인덱스는 임의 값을 가진 c4 열을 먼저 추가했다. t1_x3, t1_x4 인덱스는 고유 값을 가진 c1 열을 c3, c4 열 앞쪽에 추가했다.

-- 2
CREATE INDEX t1_x1 ON t1 (c2, c3, c4);
CREATE INDEX t1_x2 ON t1 (c2, c4, c3);
CREATE INDEX t1_x3 ON t1 (c2, c1, c3, c4);
CREATE INDEX t1_x4 ON t1 (c2, c1, c4, c3);

ANALYZE INDEX 문으로 인덱스 통계 정보를 수집하자.

-- 3
DROP TABLE index_stats_t PURGE;

ANALYZE INDEX t1_x1 VALIDATE STRUCTURE OFFLINE;
CREATE TABLE index_stats_t AS SELECT * FROM index_stats;

ANALYZE INDEX t1_x2 VALIDATE STRUCTURE OFFLINE;
INSERT INTO index_stats_t SELECT * FROM index_stats;

ANALYZE INDEX t1_x3 VALIDATE STRUCTURE OFFLINE;
INSERT INTO index_stats_t SELECT * FROM index_stats;

ANALYZE INDEX t1_x4 VALIDATE STRUCTURE OFFLINE;
INSERT INTO index_stats_t SELECT * FROM index_stats;

COMMIT;

아래는 통계 정보를 조회한 결과다. 먼저, t1_x1, t2_x2 인덱스를 비교해보면 t1_x2 인덱스의 브랜치 블록 개수(BR_BLKS)가 8개(=20-12) 적은 것을 확인할 수 있다. Covered 인덱스 생성시 임의 값 측 NDV가 높은 열을 먼저 추가하는 것이 유리함을 알 수 있다. 다음으로, 고유 값을 가진 c1 열을 추가한 t1_x3, x1_x4 인덱스를 살펴보면 브랜치 블록 개수가 15개로 동일한 것을 확인할 수 있다. 고유 값에 의해 브랜치 블록에 c3, c4 열을 저장하지 않았기 때문이다.^[각주:2] t1_x1 인덱스보다 브랜치 블록이 더 적지만 리프 블록(LF_BLKS)이 많아 저장 공간(USED_SPACE)을 더 사용한 점에 주의할 필요가 있다. 고유 값을 가진 열을 추가하는 기법은 인덱스 높이(HEIGHT)와 블록 개수(BLOCKS)를 감안하여 신중하게 적용해야 한다.

-- 4
SELECT name, height, blocks, lf_rows, lf_blks, lf_rows_len, br_rows, br_blks, br_rows_len, used_space
  FROM index_stats_t;
  
NAME  HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN USED_SPACE
----- ------ ------ ------- ------- ----------- ------- ------- ----------- ----------
T1_X1      3   6272 1000000    6124    11000000    6123      20      148510   44037430
T1_X2      3   6272 1000000    6124    11000000    6123      12       87988   43976908
T1_X3      3   7040 1000000    6834    11000000    6833      15      110866   48979588
T1_X4      3   7040 1000000    6834    11000000    6833      15      110866   48979588

4 행이 선택되었습니다.

1. INCLUDE 절에 기술한 칼럼은 리프 블록에만 저장된다. [본문으로]
2. 관련 내용은 http://hrjeong.tistory.com/226 글을 살펴보자. [본문으로]