INDEX사용시 주의

1. Suppressing

인덱스 생성 후 인덱스사용이 제한되는 경우

1) 인덱스 칼럼 변형

SELECT *    FROM EMP  WHERE SUBSTR(DNAME,1,3) = 'ABC' ;  // 인덱스변형.

-->

SELECT *   FROM EMP WHERE DNAME LIKE 'ABC%' ;

* Like 의 경우 인덱스를 '첫글자 + %'의 경우는 인덱스 사용가능하지만,  첫글자가 아닌 글자에 대한 스캔시 인덱스 사용할수 없다.

SELECT *  FROM EMP WHERE SAL * 12 = 12000000 ;

; 옵티마이저는 인덱스블록의 SAL * 12 를 한 후 테이블 액세스하는 것보다 full table scan이 빠르다고 판단하여 full table scan 수행.

-->

SELECT * FROM EMP WHERE SAL = 12000000 / 12 ;

 

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE, 'YYMMDD') ='940101' ;

-->

SELECT * FROM EMP WHERE HIREDATE = TO_DATE('940101','YYMMDD') ;

; 날짜 형식으로 설정된 칼럼에서 특정일자의 데이터를 추출하는 과정에서 

; 날짜형식에서 Range Scan 통한 추출시 date형식보다 문자형식으로

; 조건절에 자주등장하는 칼럼에 대해 날짜 형식이 아닌 문자형식으로

2) 내부적 데이터 변환

CHA 칼럼의 데이터 타입이 CHAR인 경우 내부적으로 형변환이 발생한다.

SELECT * FROM TAB WHERE CHA = 10 ;

--> 

SELECT * FROM TAB WHERE TO_NUMBER(CHA) = 10 ;

SELECT * FROM TAB WHERE NUM LIKE '7410%'; 

;NUM이 NUMBER타입인 경우

-->

SELECT * FROM TAB WHERE TO_CHAR(NUM) LIKE '7410%'; 

SELECT * FROM TAB WHERE DAT = '20040501' ;

; DAT 가 Date타입인 경우

-->

SELECT * FROM TAB WHERE DAT = to_date('01-MAY-04') ;

* 다량의 데이터를 가진 테이블에 대해 형변환이 발생하면 응답속도의 저하가 발생.

 

2. Not Operator의 사용

 

* 인덱스가 설정되어 있는 컬럼에 대해 조건절에서 NOT을 사용하면 인덱스를 사용할수 없다.

SELECT ENAME INTO :COL1

  FROM EMP

 WHERE EMPNO <> '1234'

;

-->

SELECT ENAME INTO :COL1

  FROM EMP

 WHERE NOT EXISTS ( SELECT '1' FROM EMP WHERE EMPNO ='1234' )

;

* Not(<>)연산자 대용으로 Not Exists, Minus등을 이용하여 인덱스 유도.

 

Exists Not Exists 

Not Exists는 조건절에 만족하는 데이터 발견시 바로 True를 리턴하며, 더 이상의 스캔을 발생시키지 않는 스캔 방식의 특성으로 인해 전체 범위처리가 아닌 부분 범위처리방식으로 처리됨. 인덱스에 직접적인 Not조건이나 Minus문장을 사용하는 것 보다 훨씬 나은 성능을 가지는 SQL문장을 작성할수 있다.

IN, NOT IN 처럼 Sub Query의 데이터를 Main Query 에 상수로 전달하는게 아니라 Sub Query의 데이터가 존재하는 지를 확인하는 경우 사용.

Sub Query절의 데이터가 여러건 나오더라도 최초 데이터가 추출되는 순간  더이상 데이터를 추출하지 않고, Main Query에 Return한다.

Munus

- 두집합간의 차집합을 추출

- 차집합을 추출하기 위해 내부적으로 sort 발생.(전체범위처리)

3. Null

- is not null : 인덱스사용에 제한을 받는다.

- 문자면 space, 숫자면 0보다 큰데이터를 추출하도록 유도

SELECT * FROM EMP WHERE ENAME IS NOT NULL ;

--> 

SELECT * FROM EMP WHERE ENAME > ' ' ;

SELECT * FROM EMP WHERE COMM IS NOT NULL;

-->

SELECT * FROM EMP WHERE COMM > 0;

** 인덱스는 정렬되어 들어가는 구조임.