문서유형ㅣ기술정보
분야ㅣ튜닝
적용제품버전ㅣTibero 7.2.4
문서번호ㅣTTUTI039
개요
- INDEX RANGE SCAN 은 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식입니다.
- B*Tree 인덱스의 가장 일반적인 액세스 방식입니다.
- 인덱스 스캔 범위(range)를 최소화 하는 것과 테이블로 액세스하는 횟수를 최소화 하는 것이 중요합니다.
- 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 index range scan 가능합니다.
- Index range scan과정을 거쳐 생성된 결과 집합은 인덱스 컬럼 순으로 정렬됩니다. 따라서, sort order by 연산을 생략하거나 min/max 값을 빠르게 추출할 수 있습니다.
방법
1. 테스트 오브젝트 및 데이터 생성
INDEX 스캔 방식에 따른 성능 테스트를 위해 아래 구문을 먼저 수행합니다.
-- 테스트 테이블 생성
SET ECHO ON
DROP TABLE T1;
CREATE TABLE "T1"
( "C1" NUMBER,
"C2" NUMBER,
"C3" NUMBER,
"C4" NUMBER,
"C5" NUMBER,
"C6" NUMBER,
"C7" NUMBER,
"C8" NUMBER,
"C9" NUMBER,
"C10" NUMBER
);
-- 데이터 1000만건 입력
INSERT /*+append*/ INTO T1
SELECT 1 C1
, LEVEL C2
, LEVEL C3
, LEVEL C4
, LEVEL C5
, LEVEL C6
, LEVEL C7
, LEVEL C8
, LEVEL C9
, LEVEL C10
FROM DUAL CONNECT BY LEVEL<=10000000;
COMMIT;
-- 인덱스 생성
CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);
2. 인덱스 없이 데이터를 조회하는 경우
인덱스 없이 테이블을 FULL SCAN 하였을 때 얼마나 성능 저하가 발생하는지 확인합니다.
-- 인덱스를 사용하지 않도록 설정
SQL> ALTER INDEX IDX_T1_C1C2 INVISIBLE;
Index 'IDX_T1_C1C2' altered.
-- T1 테이블에서 조건에 맞는 데이터 건수 조회 결과 약 1 초 소요됨
SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT
COUNT(*)
FROM T1
WHERE C1 = 1
AND C2 >= 10 AND C2 <= 20
;
COUNT(*)
----------
11
1 row selected.
Total elapsed time 00:00:01.016124
SQL ID: ahh910anj0fhy
Child number: 1940
Plan hash value: 1845450593
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:35685, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:35685, %%CPU:0, Rows:1)
3 TABLE ACCESS (FULL): T1 (Cost:35685, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
3 - filter: ("T1"."C2" <= 20) AND ("T1"."C1" = 1) AND ("T1"."C2" >= 10) (0.000 * 1.000 * 1.000)
Note
--------------------------------------------------------------------------------
3 - dynamic sampling used for this table (16 blocks)
NAME VALUE
------------------------------ ----------
db block gets 795
consistent gets 85067
physical reads 77214
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1
참고
조회 결과 약 1초가 소요되었습니다.
읽기 일관성 모드에서의 블록 요청 횟수(
consistent gets) 값은 85,067로 확인됩니다.physical read는 77,214건으로 확인됩니다. (
physical reads는T1테이블 Full Scan 수행 시 세그먼트의 모든 블록을 읽는 과정에서 버퍼 캐시에서 읽지 못한 경우 발생합니다.)
3. 빠른 전체 인덱스 스캔(INDEX FAST FULL SCAN) 방식을 사용하는 경우
INDEX_FFS 힌트를 사용하여 명시한 인덱스를 사용해 빠른 전체 인덱스 스캔(Fast Full Index Scan)을 하도록 지시하고 실행계획을 확인합니다.
SQL> ALTER INDEX IDX_T1_C1C2 VISIBLE;
Index 'IDX_T1_C1C2' altered.
SQL> SELECT /*+ INDEX_FFS(T1 IDX_T1_C1C2)*/
COUNT(*)
FROM T1
WHERE C1 = 1
AND C2 >= 10 AND C2 <= 20
;
COUNT(*)
----------
11
1 row selected.
Total elapsed time 00:00:00.847795
SQL ID: f8sybg63t6xdz
Child number: 1941
Plan hash value: 862723915
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:11922, %%CPU:1, Rows:1)
2 SORT AGGR (Cost:11922, %%CPU:1, Rows:1)
3 FILTER (Cost:11922, %%CPU:1, Rows:1)
4 INDEX (FAST FULL SCAN): IDX_T1_C1C2 (Cost:11693, %%CPU:0, Rows:8196822)
Predicate Information
--------------------------------------------------------------------------------
3 - filter: ("T1"."C2" <= 20) AND ("T1"."C1" = 1) AND ("T1"."C2" >= 10) (0.000 * 1.000 * 1.000)
Note
--------------------------------------------------------------------------------
4 - dynamic sampling used for this table (16 blocks)
NAME VALUE
------------------------------ ----------
db block gets 104
consistent gets 28094
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1
참고
INDEX_FFS힌트를 적용하여 실행한 경우 약 0.85초가 소요되었습니다.읽기 일관성 모드에서의 블록 요청 횟수(
consistent gets) 값은 28,094로 확인됩니다.physical read는 0건으로 확인됩니다.
4. 범위 인덱스 스캔(INDEX RANGE SCAN) 방식을 사용하는 경우
INDEX_RS 힌트를 사용하여 명시한 인덱스를 사용해 범위 인덱스 스캔(Range Index Scan)을 하도록 지시하고 실행계획을 확인합니다.
SQL> SELECT /*+ INDEX_RS(T1 IDX_T1_C1C2)*/
COUNT(*)
FROM T1
WHERE C1 = 1
AND C2 >= 10 AND C2 <= 20
/
COUNT(*)
----------
11
1 row selected.
Total elapsed time 00:00:00.000384
SQL ID: 6ma4cuhmjbtnr
Child number: 1942
Plan hash value: 2030295845
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:3, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:3, %%CPU:0, Rows:1)
3 INDEX (RANGE SCAN): IDX_T1_C1C2 (Cost:3, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
3 - access: ("T1"."C1" = 1) AND ("T1"."C2" >= 10) AND ("T1"."C2" <= 20) (1.000 * 1.000 * 0.000)
Note
--------------------------------------------------------------------------------
3 - dynamic sampling used for this table (16 blocks)
NAME VALUE
------------------------------ ----------
db block gets 0
consistent gets 3
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1
참고
INDEX_RS힌트를 적용하여 실행한 경우 약 0.00038초가 소요되었습니다.읽기 일관성 모드에서의 블록 요청 횟수(
consistent gets) 값은 3으로 확인됩니다.physical read는 0건으로 확인됩니다.