[ORACLE] 인덱스 타는 방법

인덱스를 타게하는 방법들

 

인덱스 컬럼에 대한 NOT 사용 제한
Where절 내에서 인덱스 컬럼에 대해 NOT 비교문을 사용할 경우 해당 컬럼을 가공하는 것과 동일한 효과가 나타나서 ORACLE은 NOT을 만나게 되면 해당 컬럼에 대한 인덱스를 사용하지 않는 실행계획을 수립한다. !=보다 다른 범위로 설정해서 사용하자

--인덱스 사용하지 못하는경우
SELECT …
FROM ...
WHERE A != 0;

--인덱스 사용하는 경우
SELECT …
FROM ...
WHERE A > 0;

 

INDEX 컬럼에 대한 가공

Where절에서 인덱스 컬럼이 가공되어 사용될 경우 optimizer는 해당 인덱스를 사용하지 않게 되고 사용 가능한 다른 인덱스가 없다면 이 SQL문은 full-table scan을 통해 결과값을 얻게 된다.

--컬럼이 가공된 경우 
SELECT …
FROM A
WHERE COL * 12 > 25000;

--컬럼을 가공하지 않은 경우
SELECT ...
FROM A
WHERE COL > 25000 / 12;

첫번째 사례는 COL 컬럼에 대한 가공이 이뤄져 이 컬럼에 대한 인덱스가 있더라도 사용되지 않고 full-table scan을 하게 된다.

 

두번째 사례는 COL 컬럼에 대한 인덱스가 있다면 이를 사용하여 INDEX RANGE SCAN을 하게 된다.

 

결합 INDEX의 선행 컬럼 사용과 SKIP SCAN의 활용

ORACLE 9i 이전 버전에서는 여러 컬럼으로 구성된 결합 인덱스가 있을 때, 컬럼 구성상 맨 앞에 있는 컬럼이 Where절에 사용되지 않으면 해당 인덱스를 이용한 실행 계획은 생성되지 않았다. 결합 인덱스를 사용하기 위해서는 where절에서 선행 컬럼이 가공되지 않은 채 사용되어야 하며 equal 비교로 사용되는 것이 성능을 향상 시키는 한 방법이었다. 불필요하게 full table scan에 의한 병목 현상을 Oracle9i Database의 INDEX SKIP SCAN 기능을 사용하면 해결 할 수 있다.

 

예를들어 CREATE INDEX IDX ON TABLE (A,B,C); 인덱스가 있을때 다음과 같은 사례에서는 인덱스를 사용하지 않았다. 

SELECT ...
FROM ...
WHERE B = 'BA';

하지만 9i 버전 이후 INDEX SKIP SCAN을 사용하기로 결정하면, 결합인덱스의 선행 컬럼인 값들에 대해 샘플링을 하게 되고 각각의 A컬럼에 대해 DEPTNO가 추가된 SQL을 내부적으로 실행 하게 된다.

 

 

정리

1. !=을 사용하지 말고 >0, <0 등 다른 조건으로 바꿔서 사용하기

2. 인덱스를 사용하기 위해서 컬럼을 가공하지말고 우변을 건들여라

3. 오라클 9i 버전이후는 인덱스에서 선행컬럼들을 사용하지않고 중간컬럼만 사용해도 인덱스를 사용할 수 있다