03. 다양한 인덱스 스캔방식

(1) Index Range Scan

루프블록에서 리프블록까지 수직으로 탐색 후리프 블록을 필요한 범위만 스캔
B*Tree 인덱스의 가장 일반적
인덱스르 스캔하는 범위(Range)를 얼마나 줄이느냐, 테이블 액세스 회수를 얼마나 줄이느냐가 관건
생성돤 결과집합은 인덱스 컬럼순을 정렬된 상태로 sort order by 생략하거나, mix/max 값을 빠르게 추출할 수 있음.


 select  * 
 From SF_TEST
 where  ST_DATE = '20160101'   

-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=1 Bytes=2K)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SF_TEST' (TABLE) (Cost=0 Card=1 Bytes=2K)
   2    1     INDEX (RANGE SCAN) OF 'IX_SF_TEST_ST_DATE_ED_DATE' (INDEX) (Cost=0 Card=1)

(2) Index Full Scan

수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적 탐색
최적의 인덱스 없을 경우 차선으로 선택


 select  ED_DATE
 From SF_TEST
 where  ED_DATE = '20160101'   
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
   1    0   INDEX (FULL SCAN) OF 'IX_SF_TEST_ST_DATE_ED_DATE' (INDEX) (Cost=1 Card=1 Bytes=6)

Index Full Scan 효용성

인덱스 선두컬럼이 where 절에 없더라도, 테이블 전체를 스캔하는 것보다 인덱스 스캔단계에서 대부분 레코드가 필터링하고
일부에대해서만 테이블 액세스가 발생하도록 I/O 가 효율

인덱스를 이용한 소트 연산 대체

결과집합이 인덱스 컬럼순으로 정렬되므로 Sort Order By 연산을 생략할 목적으로 옵티마이저가 전략적으로 선택


 select /*+ first_rows */ * 
 From SF_TEST
 where  ed_DATE >  '20160101'   
 ORDER BY st_DATE 
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=179 Bytes=391K)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SF_TEST' (TABLE) (Cost=2 Card=179 Bytes=391K)
   2    1     INDEX (FULL SCAN) OF 'IX_SF_TEST_ST_DATE_ED_DATE' (INDEX) (Cost=1 Card=2)

(3) Index Unique Scan

수직탐색만으로 데이터를 찾는 방식으로, '=' 조건으로 탐색하는 경우에만 작동
Unique 인덱스가 존재하는 컬럼은 중복발생하지 않으며, '=' 조건으로 검색할때 데이터 한건 찾는 순간 더이상 탐색 없음
Unique 인덱스 더라고 범위검색 조건일 경우는 Index Range Scan
Unique 결합 인덱스에 대해 일부 컬럼만 검색할때도 Index Range Scan

(4) Index Skip Scan

조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을때 유용


  select * from 사원 where 성별 = '남' and 연봉 between 2000 and 4000

브랜치 블록에서 읽은 컬럼값 정보를 이용해 조건에 "가능성이 있는" 리프 블록만 골라서 액세스

버퍼 Pinning을 이용한 Skip 원리

브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프블록을 찾는 과정을 반복
(상위 노드를 기준으로 하위노드를 NL조인 그림 연상)
루트 또는 브랜치 블록을 재방문하더라고 Pinning 한 상태이기 때문에 추가적인 I/O 발생 않음

Index Skip Scan 이 작동하기 위한 조건

최선두 컬럼은 입력하고 중간 컬럼에 대한 조건절이 누락된 경우
PK : 업종유형코드+업종코드+기준일자
where 업종유형코드 ='01' and 기준일자 between '20080501' and '20080531'
Distinct Value 개수가 적은 두개의 선두컬럼이 모두 누락
where 기준일자 between '20080501' and '20080531'
선두컬럼이 범위 조건일때
Index Skip Scan 힌트 : 유도 - index_ss, 방지-no_index_ss

In-List Iterator 와의 비교

(5) Index Fast Full Scan

인덱스 트리 구조를 무시하고 인덱스 세크먼트 전체를 Multiblock Read 방식으로 스캔

(6) Index Range Scan Descending

-Index Range Scan 동일한 스캔방식, 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합

(7) And-Equal, Index Combine, Index join

오라클은 두개 이상 인덱스를 사용하는 방법 제공

And-Equal

10g 이후 폐기
단일 컬럼에 Non-Unique 인덱스여야 함과 동시에 인덱스 컬럼 조건이 '='
테이터 분포가 좋지 않아 단독으로 테이블이 Random 액세스가 많이발생하는 인덱스를 두개이상 결합하여 테이블 액세스량을 줄임

Index Combine


select

select /*+/*+ index_combine(eindex_combine(e emp_deptno_idx emp_job_idx) */ *
from scott.emp e
where deptno = 30
and job = 'SALESMAN'

---
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=38)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=1 Bytes=38)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1)



   emp_deptno_idx emp_job_idx) */ *
from scott.emp e
where deptno = 30
  and job = 'SALESMAN'
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=38)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=1 Bytes=38)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP AND
   4    3         BITMAP CONVERSION (FROM ROWIDS)
   5    4           INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=1)
   6    3         BITMAP CONVERSION (FROM ROWIDS)
   7    6           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1)

And-Equal 과 동일하나, And-Equal 과 동일하나,
조건절이 '=' 일 필요없고, Non-Unique 인덱스일 필요도 없으며
조건절이 OR 로 결합된 경우 유용 ( deptno = 30 or job = 'SALESMAN' => BITMAP OR )

조건절이 '=' 일 필요없고, Non-Unique 인덱스일 필요도 없으며
조건절이 OR 로 결합된 경우 유용 ( deptno = 30 or job = 'SALESMAN' => BITMAP OR )

Index join

한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들때 사용 - 한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들때 사용
쿼리에 사용된 컬럼들이 인덱스에 모두 포함될때만 작동 - 쿼리에 사용된 컬럼들이 인덱스에 모두 포함될때만 작동


select /*+ index_join(e emp_deptno_idx emp_job_idx) */ deptno , job 
from scott.emp e
where deptno = 30 and job = 'SALESMAN'
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=11)
   1    0   VIEW OF 'SCOTT.index$_join$_001' (VIEW) (Cost=3 Card=1 Bytes=11)
   2    1     HASH JOIN
   3    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=1 Card=1 Bytes=11)
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=1 Bytes=11)


select /*+ index_join(e emp_deptno_idx emp_job_idx) */ deptno , job 
from scott.emp e
where deptno = 30 and job = 'SALESMAN'
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=11)
   1    0   VIEW OF 'SCOTT.index$_join$_001' (VIEW) (Cost=3 Card=1 Bytes=11)
   2    1     HASH JOIN
   3    2       INDEX (RANGE SCAN) OF 'EMP_JOB_IDX' (INDEX) (Cost=1 Card=1 Bytes=11)
   4    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=1 Bytes=11)