인덱스 기본 - INDEX SCAN종류

1. INDEX RANGE SCAN

• B*Tree인덱스의 가장 일반적이고 정상적인 형태
• 필요한 범위만 스캔
• 실행계획 : INDEX (RANGE SCAN) OF '인덱스명' (INDEX)
• 선두컬럼 가공하지않은 상태로 조건절에 사용
  - 성능은 인덱스 스캔범위, 테이블 엑세스 횟수를 얼마나 줄일수있느냐로 결정(인덱스탄다고끝이아님)
  

2. INDEX FULL SCAN

• 수직탐색 없이 리프블록 처음부터 끝까지 수평적으로 탐색하는 방식
• 실행계획 : INDEX (FULL SCAN) OF '인덱스명' (INDEX)
• 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택
• 마땅한 인덱스가없을때 조건절에서 필터후의 데이터량이 소량이라면
  인덱스풀스캔후 필터된 데이터를 대상으로 테이블에 엑세스하는것이 효율적이다.
  

3. INDEX UNIQUE SCAN

• 수직적 탐색만으로 데이터를 찾는 방식
• 등치(=)조건으로 탐색하는 경우에 작동
• 실행계획 : INDEX (UNIQUE SCAN) OF '인덱스명' (UNIQUE)
• UNIQUE INDEX라해도 범위검색조건 (BETWEEN, 부등호, LIKE)로 검색할때는 IDNEX RANGE SCAN으로 처리된다.
  

4. INDEX SKIP SCAN

• 인덱스 선두컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 TABLE FULL SCAN을선택
• TABLE FULL SCAN보다 I/O를줄이거나 정렬된 결과를 쉽게 얻을수 있다면 INDEX FULL SCAN사용
• 9i버전부터 선두컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔방식을 선보임 > skip scan
• 인덱스 선두컬럼의 DISTINCT VALUE개수가 적고 후행 컬럼의 DISTINCT VALUE갯수가 많을때 유용
  ex) 성별 - 고객번호 인덱스
• 힌트 : index_ss, no_idex_ss(방지)

  -- index_ss 힌트 사용.
  select /+ index_ss(사원 사원_IDX) / *
  from 사원
  where 연봉 between 2000 and 4000
  

• 1번 블록은 성별 남 보다 작은 성별이있는지 확인하기 위함
• 남&1000>= 블록은 안타도되는거지만 컴퓨터는 성별이 남,여만있다는것을모르기때문에
  중간에 해당하는 값이 있는지 확인하기위해 접근한다. (10번블록도 마찬가지)
• 

인덱스 skip scan이 작동하기 위한 조건

• 인덱스 일별업종별거래_PK : 업종유형코드 + 업종코드 + 기준일자
• 아래 SQL처럼 선두컬럼에 대한 조건절은 있고, 중간컬럼(=업종코드)에 대한 조건이 없는 경우에도 SKIP SCAN사용할 수 있다.

  SELECT /*+ INDEX_SS(A 일별업종별거래_PK) */
  기준일자, 업종코드, 체결건수, 체결수량, 거래대금
  FROM 일별업종별 거래 A
  WHERE 업종유형코드 = '01'
  AND 기준일자 BETWEEN '20080501' AND '20080531'

• Distinct Value 개수가 적은 두 개의 선두컬럼이 모두 조건절에 없어도 Skip Scan사용가능
• 선두컬럼이 부등호, BETWEEN, LIKE같은 범위검색 조건일 때도 SKIP SCAN사용가능.

5. INDEX FAST FULL SCAN

• INDEX FULL SCAN보다 빠르다 > 논리적 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 MULTIBLOCK I/O방식으로 스캔하기 때문
• 힌트 : INDEX_FFS, NO_INDEX_FFS
• INDEX FULL SCAN은 논리적 구조에따라 읽지만, FFS는 물리적으로 디스크에 저장된 순서대로 리프블록들을 읽어들인다.
• 디스크로 부터 대량의 인덱스블록을 읽어야할 때 큰 효과를 발휘
• 속도는 빠르지만, 인덱스 리프노드가 갖는 연결 리스트 구조를 무시한채 읽기 때문에 인덱스 키순서대로 정렬되지 않는다.
• 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼 있을때만 사용할 수 있다
• 인덱스가 파티션 돼 있지않아도 병렬쿼리가능
• 병렬쿼리시에는 DIRECT PATH I/O방식을 사용
• 요약 : 세그먼트 전체 스캔, 순서보장 안됨, MULTIBLOCK I/O, 병렬스캔가능, 인덱스에 포함된컬럼으로만 조회할때 사용가능

6. INDEX RANGE SCAN DESCENDING

• INDEX RANGE SCAN과 기본적으로 동일
• 뒤에서부터 앞쪽으로 스캔하기때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만다르다.
• 실행계획 : INDEX (RANGE SCAN DESCENDING) OF '인덱스명' (INDEX(UNIQUE))

  SELECT * FROM EMP
  WHERE EMPNO > 0
  ORDER BY EMPNO DESC

• 아래처럼 MAX값을 구할때에도 해당컬럼에 인덱스가 있으면 인덱스를 뒤에서부터 한건만 읽고 멈추도록 실행계획이 자동으로 수립

  -- 쿼리
  SELECT DEPTNO, DNAME, LOC
          
  , (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)
  FROM DEPT D
  -- 실행계획
  FIRST ROW
    INDEX(RANGE SCAN (MIN/MAX)) OF '인덱스명' (INDEX)