인덱스와 테이블 액세스의 세 가지 원리

튜닝의 기본은 인덱스와 테이블 액세스의 효율을 높이는 것이다. 이와 관련된 세 가지 원리를 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1, MOD (ROWNUM, 100) AS c2, LPAD ('X', 500) AS c3 FROM XMLTABLE ('1 to 100000');

CREATE INDEX t1_x1 ON t1 (c1);

첫 번째 원리는 불필요한 Random 액세스를 줄이는 것이다. 인덱스와 테이블의 반환 행수(A-Rows)를 비교하면 불필요한 Random 액세스의 발생 여부를 판단할 수 있다. 아래 쿼리는 인덱스에서 50,000건이 조회되었지만, 테이블에서 대부분 필터링되고 500건의 결과만 반환된다. 불필요한 Random 액세스가 49,500회(=50000-500)나 발생한 것이다.

-- 2-1
SELECT /*+ INDEX(T1) */ COUNT (c3) FROM t1 WHERE c1 > 50000 AND c2 = 1;

-----------------------------------------------------------------
| Id  | Operation                    | Name  | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |    3686 |
|   1 |  SORT AGGREGATE              |       |      1 |    3686 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |    500 |    3686 | -- ↑
|*  3 |    INDEX RANGE SCAN          | T1_X1 |  50000 |     114 | -- ↑
-----------------------------------------------------------------

2-1번 유형에 대한 해법은 인덱스에 Random 액세스를 줄일 수 있는 칼럼을 추가하는 것이다. 아래와 같이 인덱스를 변경하자.

-- 2-2
DROP INDEX t1_x1;
CREATE INDEX t1_x1 ON t1 (c1, c2);

아래 쿼리에서 Random 액세스 발생량이 감소한 것을 확인할 수 있다. 두 번째 원리는 인덱스 스캔의 효율성(Sequential 액세스의 선택도)를 높이는 것이다. 인덱스의 반환 행수(A-Rows)와 블록 I/O(Buffers)를 비교하면 인덱스 스캔의 효율성을 확인할 수 있다. 아래 쿼리는 인덱스에서 500건을 조회하기 위해 135개의 블록을 액세스했다. 블록당 3.7개의 행(=500/135)을 조회한 것인데 이는 매우 낮은 선택도다.^[각주:1]

-- 3-1
SELECT /*+ INDEX(T1) */ COUNT (c3) FROM t1 WHERE c1 > 50000 AND c2 = 1;

-----------------------------------------------------------------
| Id  | Operation                    | Name  | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |     635 |
|   1 |  SORT AGGREGATE              |       |      1 |     635 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    500 |     635 |
|*  3 |    INDEX RANGE SCAN          | T1_X1 |    500 |     135 | -- →
-----------------------------------------------------------------

3-1 유형에 대한 해법은 인덱스의 순서를 변경하는 것이다. 아래와 같이 인덱스를 변경하자.

-- 3-2
DROP INDEX t1_x1;
CREATE INDEX t1_x1 ON t1 (c2, c1);

아래 쿼리에서 인덱스 스캔의 효율성이 높아진 것을 확인할 수 있다. 마지막 세 번째 원리는 테이블 액세스의 효율성을 높이는 것이다. 테이블의 반환 행수(A-Rows)와 블록 I/O(Buffers)를 비교하면 테이블 액세스의 효율성을 확인할 수 있다. 아래 쿼리는 테이블에서 500건을 조회하기 위해 503개의 블록을 액세스했다. 1건당 1블록을 액세스했으므로 클러스터링 팩터가 좋지 않다는 것을 알 수 있다.

-- 4-1
SELECT /*+ INDEX(T1) */ COUNT (c3) FROM t1 WHERE c1 > 50000 AND c2 = 1;

-----------------------------------------------------------------
| Id  | Operation                    | Name  | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |     503 |
|   1 |  SORT AGGREGATE              |       |      1 |     503 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    500 |     503 | -- →
|*  3 |    INDEX RANGE SCAN          | T1_X1 |    500 |       3 |
-----------------------------------------------------------------

4-1 유형에 대한 해법은 테이블을 재구성을 통해 클러스터링 팩터를 높이는 것이다.^[각주:2] 아래와 같이 테이블을 재구성하자.

-- 4-2
DROP TABLE t2 PURGE;
CREATE TABLE t2 AS SELECT * FROM t1;
TRUNCATE TABLE t1;
INSERT INTO t1 SELECT * FROM t2 ORDER BY c2, c1;
COMMIT;

아래 실행 계획은 비효율이 없다.

1. 인덱스에서 500건, 테이블에서 500건이 반환되었으므로 불필요한 Random 액세스가 존재하지 않고,

2. 인덱스도 5블록을 액세스하여 500건을 조회했으므로 인덱스 스캔 비효율도 없으며,

3. 테이블도 500건을 조회하는데 74블록을 액세스했으므로 테이블 액세스의 효율성도 양호하다.

-- 5
SELECT /*+ INDEX(T1) */ COUNT (c3) FROM t1 WHERE c1 > 50000 AND c2 = 1;

-----------------------------------------------------------------
| Id  | Operation                    | Name  | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      74 |
|   1 |  SORT AGGREGATE              |       |      1 |      74 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    500 |      74 | -- ↑    →
|*  3 |    INDEX RANGE SCAN          | T1_X1 |    500 |       5 | -- ↑ →
-----------------------------------------------------------------

1. 한 블록이 8KB이므로 인덱스 키 길이가 10바이트인 경우 약 810개의 행이 저장될 수 있다. [본문으로]
2. 특정 인덱스 구성 칼럼의 순서로 테이블을 재구성하면 다른 인덱스의 클러스터링 팩터가 나빠질 수 있으므로 주의해야 한다. [본문으로]