[ORACLE] 인덱스 사용이 불가능한 경우와 인덱스 스캔방식의 종류

1. 인덱스 사용이 불가능한 경우

 - 인덱스 컬럼을 조건절에서 가공하면 정상적으로 인덱스 사용이 불가능 하다. 

 - 부정형 비교또한 인덱스 사용이 불가능 하다.

 - IN NOT NULL 조건의 경우는 부정형 비교로 볼수 있다. 허나, 인덱스 범위 스캔이 불가능 하지만 인덱스 사용이 완전
   불가능 한 것은 아니다. 오라클은 단일 컬럼 인덱스에 대해서는 NULL 값을 저장하지 않기 때문에 IN NOT NULL
   조건의 컬럼이 단일 컬럼 인덱스 라면 INDEX FULL SCAN이 사용된다.

 - 또한 IS NULL 조건만으로도 검색시 정상적인 인덱스 사용이 불가능 하다. 하지만 NOT NULL 제약조건이 있는 경우
   옵티마이저가 IS NULL 조건을 검색해도 만족하는 레코드가 하나도 없음을 알기에 INDEX RANGE 스캔을 할 수 있다.

2. 인덱스 스캔 방식의 종류

1) INDEX RANGE SCAN

 - 가장 기본적인 방식의 인덱스 스캔이다. 

2) INDEX FULL SCAN

 - 수직적 탐색없이 인덱스 리프블록을 처음부터 끝까지 탐색하는 것을 말하며 최적의 인덱스가 없을 때 사용하는
   차선책이다  보통 인덱스 선두컬림이 조건절에 없으면 옵티마이저는 우선적으로 TABLE FULL SCAN을
   고려하지만 대용량 테이블에서의 TABLE FULL SCAN이 부담이 된다면 INDEX FULL SCAN을 생각하게 된다.

3) INDEX UNIQUE SCAN

 - 수직적 탐색으로만 데이터를 찾는 방식으로서 UNIQUE INDEX 를 통해  ' = ' 조건으로 검색하는 경우에 작동한다.
   UNIQUE INDEX라도 범위조건일경우(BETWEEN, LIKE 등)는 INDEX RANGE SCAN이 된다. 

   그리고 UNIQUE 결합 인덱스에 대한 일부컬럼만 검색이 될때도 INDEX RANGE SCAN이 이루어진다.
   예를 들어 주문상품 PK 인덱스가 [ 주문일자 + 고객ID + 상품ID ] 로 구성되어 있는데 주문일자와 고객ID로만
   검색하는 경우 INDEX RANGE SCAN이 일어난다. 

4) INDEX SKIP SCAN

 - 조건절에서 INDEX 선두컬럼이 빠진경우 인덱스 선두컬럼의 DISTINCT VALUE 개수가 적고 후행 컬럼의
   DISTINCT VALUE개수가 많을때 유용한 방식이다. 

 - INDEX SKIP SCAN은 첫번째 리프블록과 마지막 리프블록을 항상 방문한다.

 - INDEX 컬럼 중 중간 컬럼이 빠져도 INDEX SKIP SCAN이 가능하다.

 - 그리고 선두컬럼이 부등호, BETWEEN, LKIE와 같은 범위검색 조건일 때도 INDEX SKIP SCAN이 사용될 수 있다. 

 - 선두컬럼이 범위검색이어서 나머지 검색조건을 만족하는 데이터들이 서로 멀리 떨어져 있을때가
    INDEX SKIP SCAN으로써 효과를 볼 수 있다.

5) INDEX FAST FULL SCAN

 - 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 multiblock read 방식으로 스캔하기 때문에
   INDEX FULL SCAN 보다 속도가 빠르다.

 - 인덱스 리프노드가 갖는 구조를 이용하지 않기 때문에 얻어진 결과 집합이 인덱스 키 순으로 정려되지는 않는다.

 - 버퍼캐시 히트율이 낮아 디스크 I/O가 많이 발생할때 유리한 방식이다. 

6) INDEX RANGE SCAN DESCENDING

 - INDEX RANGE SCAN과 동일한 방식이나 인덱스 뒤에서부터 앞으로 탐색하기 때문에 내림차순
   결과집합을 얻는다.. 

★ 위 글은 '오라클 성능고도화 원리와 해법 2' 를 보고 공부한 내용을 정리한 것 입니다.
    자세한 내용은 책을 참고하세요.

-끝-