Oracle/Tuning2020. 2. 23. 11:34

INDEX 힌트의 세 가지 방식

INDEX 힌트는 세 가지 방식으로 사용할 수 있다. 방식에 따른 장단점을 살펴보자.


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

1
2
3
4
5
6
7
8
-- 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);

아래는 온라인 문서의 인덱스 구문이다. 테이블만 기술하면 전체 인덱스, 인덱스를 기술하면 기술한 인덱스 중에서 최적의 인덱스를 선택한다. 전자를 테이블 기술 방식, 후자를 인덱스 기술 방식이라고 하자.

1
/*+ INDEX ([@queryblock] tablespec [indexspec [indexspec]...]) */

아래 쿼리는 INDEX 힌트에 테이블만 기술했다. 조건에 따라 최적의 인덱스가 선택된다. Outline Data를 보면 온라인 문서와 달리 테이블 (칼럼, [칼럼]...) 형식의 칼럼 기술 방식이 사용된 것을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 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번 쿼리는 선두 칼럼이 일치하는 인덱스가 없어 힌트가 무시되었다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 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 인덱스를 사용했다.

1
2
3
4
5
6
7
8
9
10
11
12
-- 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한다. 테이블 기술 방식은 최적의 인덱스가 없는 경우 쿼리의 성능이 저하될 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 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한다. 인덱스 기술 방식도 인덱스가 변경되는 경우 쿼리의 성능이 저하될 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 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
Posted by 정희락_

티스토리툴바