다양한 인덱스 스캔 방식,. (by hj4867) [2014.06.20]
Contents
(1) Index Range Scan
- 인덱스를 수직적으로 탐색한 후에 리프 블록에서 "필요한 범위만" 스캔하는 방식이다.
- 실행계획 상에 Index Range Scan이 나타난다고 해서 항상 빠른 속도를 보장하는 것은 아니다.
- 인덱스 설계와 SQL튜닝의 핵심원래 : 인덱스를 스캔하는 범위(Range)를 얼마만큼 줄일 수 있느냐 그리고 테이블로 엑세스하는 횟수를 얼마만큼 줄일 수 있느냐
- 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 Index Range Scan 이 가능하다.
- 인덱스 컬럼 순으로 정렬된 상태가 되기 때문에, sort order by 연산을 생략하거나 min/max 값을 빠르게 추출할 수 있다.
SQL> create index emp_deptno_idx on emp(deptno);
인덱스가 생성되었습니다.
SQL> set autotrace traceonly explain
SQL> select * from emp where deptno = 20;
Execution Plan
----------------------------------------------------------
Plan hash value: 2468466201
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=20)
(2) Index Full Scan
- 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
- 최선의 인덱스가 없을 때 차선으로 선택된다.
SQL> create index emp_idx on emp(ename, sal);
인덱스가 생성되었습니다.
SQL> set autotrace traceonly exp
SQL> select * from emp
2 where sal > 2000
3 order by ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 737262432
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 370 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 10 | 370 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | EMP_IDX | 10 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL">2000)
filter("SAL">2000)
- h5. Index Full Scan의 효용성
- 인덱스 선두 컬럼(ename)이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려한다.
- Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있을 경우 Index Full Scan 선택한다.
- {code:sql}
SQL> select * from emp where sal > 5000 order by ename;
Execution Plan
--
Plan hash value: 737262432
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---
| 0 | SELECT STATEMENT | 1 | 37 | 2 (0) | 00:00:01 | |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 37 | 2 (0) | 00:00:01 |
| INDEX FULL SCAN | EMP_IDX | 1 | 1 (0) | 00:00:01 |
---
Predicate Information (identified by operation id):
---
2 - access("SAL">5000)
filter("SAL">5000)
** 연봉이 5,000을 초과하는 사원이 전체 중 극히 일부라면 Table Full Scan 보다는 Index Full Scan을 통한 필터링이 큰 효과를 가져다준다.
* h5. 인덱스를 이용한 소트 연산 대체 : first_rows 힌트
** 전체 집합 중 처음 일부만을 빠르게 리턴해야하므로 옵티마이저는 전략적으로 Index Full Scan 수행
** 만약 결과 집합이 많을 경우 데이터 읽기를 멈추지 않고 끝까지 fetch한다면 인덱스 스캔이 테이블 스캔보다 불리 : 많은 I/O를 일으키면서 서버 자원을 낭비 초래
h3. (3) Index Unique Scan
!img_3_03.png!
* 수직적 탐색만으로 데이터를 찾는 스캔 방식이다.
* 인덱스를 통해 '=' 조건으로 탐색하는 경우에 작동한다.
* Unique 인덱스가 존재하는 컬럼은 중복 값이 발생하지 않도록 DBMS가 정합성을 관리해 준다.
{code:sql}
SQL> select empno, ename from emp where empno = 7788;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
- 단, Unique 인덱스더라도 범위검색 조건(between, 부동호, like) 으로 검색할 때는 Index Range Scan으로 처리된다.
- 또한, Unique 결합 인덱스에 대해 일부 컬럼만으로 검색할 때도 Index Range Scan으로 나타난다.
(4) Index Skip Scan
- 인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 새로운 스캔방식(9i 부터)
- 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.
- Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 "가능성이 있는" 리프 블록만 골라서 엑세스하는 방식이다.
- 첫 번째 리프 블록 : 옵티마이저는 성별에 '남'과 '여' 두개의 값만 존재한다는 사실을 모르기에, 가장 좌측에 있는 리프 블록은 항상 방문한다.
- 두 번째 리프 블록 : 남&800 이상이면서 남&1500 미만인 레코드를 담고 있다. 따라서 조건에 맞는 값이 존재할 가능성이 없기 때문에 Skip한다.
- 세 번째 리프 블록 : 남&1500 이상이면서 남&5000 미만인 레코드는 조건에 부합하기에 방문한다.
- 네 번째, 다섯 번째 리프 블록 : 남&5000 이상인 데이터들이기에 Skip 한다.
- 여섯 번째 리프 블록(중요) : '남'의 구간이 끝났지만, '여' 중에서 연봉 < 3000 이거나 '남'과 '여' 사이에 다른 성별이 혹시 존재한다면 이 리프 블록에 저장되기에 방문한다.
- 일곱 번째 리프 블록 : 방문한다.
- 여덟 번째, 아홉 번째 리프 블록 : 방문하지 않는다.
- 열 번째 리프 블록 : '여'보다 값이 큰 미지의 성별 값이 존재한다면 여기에 담길 것이므로 방문한다.
- Index Skip Scan은 첫 번째 리프 블록과 마지막 리프 블록은 항상 방문한다.
- h5. 버퍼 Pinning을 이용한 Skip 원리
- 리프 블록에 있는 정보만으로 다음에 방문해야할 블록을 찾는 방법은 없다. 항상 그 위쪽에 있는 브랜치 블록을 재방문해서 다음 방문할 리프 블록에 대한 주소 정보를 얻어야 한다.
- 이때, 브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정을 반복한다.
- h5. Index Skip Scan이 작동하기 위한 조건
- 인덱스 맨 선두 컬럼이 누락 됐을 때만 Index Skip Scan이 작동하는 것은 아니다.
- 일일업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자
- 위처럼 인덱스가 구성되어 있다면, 최선두 컬럼(=업종유형코드)은 입력하고 중간 컬럼(=업종코드)에 대한 조건절이 누락된 경우에도 Skip Scan이 사용될 수 있다.
(5) Index Fast Full Scan
- 말그대로 Index Full Scan보다 빠르다. 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문이다.
- 논리적 순서에 따라 배치된 그림
- 물리적 순서에 따라 배치된 그림
- Index Full Scan에서는 인덱스의 논리적 구조를 따라 루트 -> 브랜치1 -> 1 -> 2 -> 3 -> 4 -> 5 -> 6 -> 7 -> 8 -> 9 -> 10번 순으로 블록을 읽어들인다.
- 반면, Index Fast Full Scan에서는 물리적으로 디스크에 저장된 순서대로 처음에 Multiblock Read 방식으로 1 -> 2 -> 10 -> 3 -> 9번 순으로 읽고, 그 다음엔 8 -> 7 -> 4 -> 5 -> 6번 순으로 읽는다.
- 트레이스 결과
- Index Full Scan 결과
- Index Fast Full Scan 결과
- Index Full Scan보다 Index Fast Full Scan이 5배 가량 빠르게 수행.
- db file scattered read 횟수 현저하게 감소.
Index Fast Full Scan의 특징
- 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 multiblock read 방식으로 스캔
- multiblock read 방식 : 디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과 발휘
- 물리적으로 디스크에 저장된 순서대로 인덱스 블록을 읽어 들임
- 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리 가능(병렬 쿼리 시 direct path read 방식 사용하므로 I/O속도가 더 빨라짐)
- 단점
- 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과집합이 인덱스 키 순서대로 정렬되지 않음
- 쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함 되어 있을 때만 사용 가능
- 관련 힌트 : index_ffs, no_index_ffs
- Index Full Scan vs. Index Fast Full Scan
| Index Full Scan | Index Fast Full Scan |
|---|---|
| 1. 인덱스 구조를 따랄 스캔 2. 결과 집합 순서 보장 3. Single Block I/O 4. 병렬스캔 불가(파티션 되어 있지 않을 경우) 5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능 | 1. 세그먼트 전체를 스캔 2. 결과집합 순서 보장 안 됨 3. multiblock I/O 4. 병렬스캔 가능 5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능 |
(6) Index Range Scan Descending
- Index Range Scan과 기본적으로 동일한 스캔방식이다.
- 그림처럼 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
(7) And-equal, Index Combine, Index Join
- 두 개 이상 인덱스를 함께 사용할 수 있는 방법
- And-Equal
- 8i도입되어 10g 부터 폐기 됨
- 인덱스 스캔량이 아무리 많더라도 두 인덱스를 결합하고 나서의 테이블 액세스량이 소량일 때 효과 있음
- 조건 : 단일 컬럼의 non-unique index & 인덱스 컬럼에 대한 조건절이 "="이어야 함
- Test
set autotrace traceonly explain
select /*+ and_equal(e emp_deptno_idx emp_job_idx) */ * from emp e where deptno=30 and job='SALESMAN';
Execution Plan
----------------------------------------------------------
Plan hash value: 2839249562
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 3 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
3 - access("JOB"='SALESMAN')
4 - access("DEPTNO"=30)
- Index Combine : 비트맵 인덱스 이용
- 목적 : 데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 random 액세스량 줄이기
- 조건절이 "='일 필요가 없고, non-unique index일 필요가 없음
- "_b_tree_bitmap_plans=true"일 때만 작동. 9i 이후는 true가 기본값임
- Test
set autotrace traceonly explain
select /*+ index_combine(e emp_deptno_idx emp_job_idx) */ * from emp e where deptno=30 and job='SALESMAN';
Execution Plan
----------------------------------------------------------
Plan hash value: 2413831903
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 32 | 4 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("JOB"='SALESMAN')
7 - access("DEPTNO"=30)
1단계 : 일반 B*Tree 인덱스를 스캔하면서 각 조건에 만족하는 레코드의 rowid 목록 얻기(INDEX RANGE SCAN)
2단계 : 1단계에서 얻은 rowid목록을 가지고 비트맵 인덱스 구조 만들기(BITMAP CONVERSION FROM ROWIDS)
3단계 : 비트맵 인덱스에 대한 bit-wise operation 수행(BITMAP AND)
4단계 : bit-wise operation 수행 결과가 true인 비트 값들을 rowid 값으로 환산해 최종적으로 방문할 테이블 rowid 목록 얻기(BITMAP CONVERSION TO ROWIDS)
5단계 : rowid를 이용해 테이블 액세스(TABLE ACCESS BY INDEX ROWID)
- Index Join
- 한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들 때 사용하는 인덱스 스캔 방식
- Hash join 매카니즘 사용
- 쿼리에 사용된 컬럼들이 인덱스에 모두 포함될 때만 작동(둘 중 어느 한쪽에 포함 되기만 하면 됨)
- Test
set autotrace traceonly explain
select /*+ index_join(e emp_deptno_idx emp_job_idx) */ * from emp e where deptno=30 and job='SALESMAN';
Execution Plan
----------------------------------------------------------
Plan hash value: 2211876416
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IDX | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=30)
2 - access("JOB"='SALESMAN')
set autotrace traceonly explain
select /*+ index_join(e emp_deptno_idx emp_job_idx) */ deptno,job from emp e where deptno=30 and job='SALESMAN';
Execution Plan
----------------------------------------------------------
Plan hash value: 3557895725
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 9 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| EMP_JOB_IDX | 1 | 9 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| EMP_DEPTNO_IDX | 1 | 9 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
2 - access(ROWID=ROWID)
3 - access("JOB"='SALESMAN')
4 - access("DEPTNO"=30)
1단계 : 크기가 비교적 작은 쪽 인덱스(emp_job_idx)에서 키 값과 rowid를 얻어 PGA 메모리에 해시 맵 생성
(해시 키 = rowid 가 사용)
2단계 : 다른 쪽 인덱스(emp_dept_idx)를 스캔하면서 먼저 생성한 해시 맵에 같은 rowid 값을 갖는 레코드가 있는지 탐색
3단계 : rowid끼리 조인에 성공한 레코드만 결과집합에 포함 : 이 rowid가 가리키는 테이블은 각 인덱스 컬럼에 대한 검색 조건을 모두 만족한다