1. HOME
  2. [종료]주주클럽 스터디
  3. 2016년 하반기 오라클 성능고도화 원리와 해법 I,II 스터디
  4. 3. 다양한 인덱스 스캔 방식

3. 다양한 인덱스 스캔 방식 (by wooa1201) [2017.03.15]

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 이 작동하기 위한 조건

  1. 최선두 컬럼은 입력하고 중간 컬럼에 대한 조건절이 누락된 경우
    PK : 업종유형코드+업종코드+기준일자
    where 업종유형코드 ='01' and 기준일자 between '20080501' and '20080531'
  2. Distinct Value 개수가 적은 두개의 선두컬럼이 모두 누락
    where 기준일자 between '20080501' and '20080531'
  3. 선두컬럼이 범위 조건일때
  4. 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)
  1. HOME
  2. [종료]주주클럽 스터디
  3. 2016년 하반기 오라클 성능고도화 원리와 해법 I,II 스터디
  4. 3. 다양한 인덱스 스캔 방식