INDEX 힌트는 세 가지 방식으로 사용할 수 있다. 방식에 따른 장단점을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2, ROWNUM AS c3, ROWNUM AS c4 FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t1_x2 ON t1 (c2, c3);
CREATE INDEX t1_x3 ON t1 (c1, c2, c3);
CREATE INDEX t1_x4 ON t1 (c1, c2, c4);
아래는 온라인 문서의 인덱스 구문이다. 테이블만 기술하면 전체 인덱스, 인덱스를 기술하면 기술한 인덱스 중에서 최적의 인덱스를 선택한다. 전자를 테이블 기술 방식, 후자를 인덱스 기술 방식이라고 하자.
/*+ INDEX ([@queryblock] tablespec [indexspec [indexspec]...]) */
아래 쿼리는 INDEX 힌트에 테이블만 기술했다. 조건에 따라 최적의 인덱스가 선택된다. Outline Data를 보면 온라인 문서와 달리 테이블 (칼럼, [칼럼]...) 형식의 칼럼 기술 방식이 사용된 것을 확인할 수 있다.
-- 2-1
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 = 1;
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 |
|* 2 | INDEX RANGE SCAN | T1_X1 |
-----------------------------------------------------
Outline Data
-------------
/*+
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
*/
-- 2-2
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 = 1 AND c2 = 1;
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 |
|* 2 | INDEX RANGE SCAN | T1_X3 |
-----------------------------------------------------
Outline Data
-------------
/*+
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2" "T1"."C3"))
*/
아래 쿼리는 INDEX 힌트를 칼럼 기술 방식으로 사용했다. 칼럼 순서와 선두 칼럼이 일치하는 인덱스 중에서 최적의 인덱스가 선택된다. 3-4번 쿼리는 선두 칼럼이 일치하는 인덱스가 없어 힌트가 무시되었다.
-- 3-1 SELECT /*+ INDEX(T1 (C1)) */ * FROM t1 WHERE c1 = 1; ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | |* 2 | INDEX RANGE SCAN | T1_X1 | ----------------------------------------------------- -- 3-2 SELECT /*+ INDEX(T1 (C1 C2)) */ * FROM t1 WHERE c1 = 1; ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | |* 2 | INDEX RANGE SCAN | T1_X3 | ----------------------------------------------------- -- 3-3 SELECT /*+ INDEX(T1 (C1 C2 C4)) */ * FROM t1 WHERE c1 = 1; ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | |* 2 | INDEX RANGE SCAN | T1_X4 | ----------------------------------------------------- -- 3-4 SELECT /*+ INDEX(T1 (C1 C2 C3 C4)) */ * FROM t1 WHERE c1 = 1; ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | |* 2 | INDEX RANGE SCAN | T1_X1 | -----------------------------------------------------
아래 쿼리는 INDEX 힌트를 세 가지 방식으로 사용했다. 세 쿼리 모두 t1_x2 인덱스를 사용했다.
-- 4 SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c2 = 1; SELECT /*+ INDEX(T1 T1_X2) */ * FROM t1 WHERE c2 = 1; SELECT /*+ INDEX(T1 (C2)) */ * FROM t1 WHERE c2 = 1; ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | |* 2 | INDEX RANGE SCAN | T1_X2 | -----------------------------------------------------
t1_x2 인덱스를 삭제하고 쿼리를 다시 수행해보자. 5-1번 쿼리는 t1_x3 인덱스를 FULL SCAN하고, 5-2, 5-3번 쿼리는 힌트가 무시되어 테이블을 FULL SCAN한다. 테이블 기술 방식은 최적의 인덱스가 없는 경우 쿼리의 성능이 저하될 수 있다.
-- 5-1 DROP INDEX t1_x2; SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c2 = 1; ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | |* 2 | INDEX FULL SCAN | T1_X3 | -- ! ----------------------------------------------------- -- 5-2 SELECT /*+ INDEX(T1 T1_X2) */ * FROM t1 WHERE c2 = 1; ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| T1 | ---------------------------------- -- 5-3 SELECT /*+ INDEX(T1 (C2)) */ * FROM t1 WHERE c2 = 1; ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| T1 | ----------------------------------
t1_x2 인덱스를 c3, c2 순서로 생성하고 쿼리를 다시 수행해보자. 6-1, 6-2번 쿼리는 t1_x2 인덱스를 FULL SCAN하고, 6-3번 쿼리는 힌트가 무시되어 테이블을 FULL SCAN한다. 인덱스 기술 방식도 인덱스가 변경되는 경우 쿼리의 성능이 저하될 수 있다.
-- 6-1 CREATE INDEX t1_x2 ON t1 (c3, c2); SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c2 = 1; --------------------------------------------- | Id | Operation | Name | --------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | |* 2 | INDEX FULL SCAN | T1_X2 | -- ! --------------------------------------------- -- 6-2 SELECT /*+ INDEX(T1 T1_X2) */ * FROM t1 WHERE c2 = 1; --------------------------------------------- | Id | Operation | Name | --------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | |* 2 | INDEX FULL SCAN | T1_X2 | -- ! --------------------------------------------- -- 6-3 SELECT /*+ INDEX(T1 (C2)) */ * FROM t1 WHERE c2 = 1; ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| T1 | ----------------------------------
지금까지 살펴본데로 INDEX 힌트는 칼럼 기술 방식으로 사용하는 편이 가장 바람직하다. 인덱스 변경을 감안하여 스캔 효율을 만족하는 범위에서 최소의 칼럼을 기술하도록 하자.
'Oracle > Tuning' 카테고리의 다른 글
| 조인에 따른 사용자 함수의 동작 (0) | 2020.03.21 |
|---|---|
| 월 기준 이력 조회 (0) | 2020.03.16 |
| NOT EXISTS 서브 쿼리가 UNNEST되지 않는 사례 (0) | 2020.02.21 |
| BATCH NL 조인과 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 (0) | 2020.02.18 |
| JPPD에 의한 조인 순서의 이상 동작 (0) | 2020.02.12 |
