Oracle - Index와 관련된 힌트들

Index와 관련된 힌트들 

 

 

이미 앞선 여러 강좌에서 인덱스와 관련된 힌트를 살펴보았지만... 

이번엔 묶어서 간단히 개념을 살펴보도록 하겠습니다.

개발을 하다보면 SQL문을 자주 사용하게 되는데... 

최소한 Toad등에서 Query에 대해 Plan한번만 떠 보세요... 그러면 그 쿼리가 어느 정도의 성능을 내는지는 대충 짐작하실 수 있습니다. 그런 다음 테이블의 인덱스를 적절히 사용하는지 확인만 하면 최소한 나쁜 쿼리는 작성하지 않게 됩니다. (인덱스를 사용하는 경우가 최선의 성능을 보장 하지는 않습니다)

아래 내용을 참고하시고 사용하는 DB가 오라클이라면 반드시 힌트에 대해 관심을 가지시기 바라구요 옵티마이저 수행 경로가 정상적인 경우와 비정상인 경우 성능 차이는 장난 아닙니다. 심지어 10배 이상 나는 경우도 있구요,,,

(힌트는 Oracle Optiomizer의 수행경로를 변경 시키는 역할을 합니다.)

인덱스 힌트를 사용하게 되면 오라클 옵티마이저는 기술된 인덱스를 써야 한다는 것을 알게되며 이 경우 Table Full Scan을 하지 않게 되죠... 물론 사용되는 인덱스에 대해 COST는 계산하지 않고 존재하는 인덱스라면 사용을 한다는 이야깁니다...

만약 인덱스 힌트가 사용되지 않았지만 인덱스를 사용할 수 있는 경우라면 여러 인덱스중 COST가 적게 드는 인덱스를 하나 선택하여 사용하게 되구요...

사용 형식은 아래와 같구요...

/*+ INDEX ( table [index [index]...] ) */

table은 테이블 명이나 Alias 명 입니다.

index에는 사용되는 인덱스명을 쓰시면 되구요,

예)

select /*+ INDEX(idx_emp_job) */ 

       ename, 

       job 

from emp 

where job= '프로그래머';

DELETE /*+ INDEX(idx_emp_status)*/ 

FROM emp_status

WHERE status = '휴직';

*** INDEX_ASC : INDEX 힌트와 거의 유사하며 단지 다른 부분이라면 인덱스 영역의 검색되는 데이터가 오름차순으로 Scan 되는 것이죠...

INDEX_ASC

/*+ INDEX_ASC(table index[index .. index]) */

*** INDEX_DESC : 이 힌트 역시 INDEX힌트와 유사하며 단지 인덱스 영역의 검색되는 데이터가 내림차순으로 Scan 되는 것이죠...

/*+ INDEX_DESC (table index[index .. index]) */

위 INDEX_ASC나 INDEX_DESC를 적절히 사용하시면 ORDER BY 구문을 사용하지 않아도 되는 경우가 대부분 입니다.(ORDER BY를 많은 건의 데이터에 대해 사용하시면 성능에 악 영향을 줍니다)

emp 테이블에 ename으로 인덱스가 있다면...

select ename, sal

from emp

order by ename

==>

select /*+ index_asc(emp) */

         ename, sal

from emp

where ename > '가'  -- 한글 이름을 사용한다고 했을 때

(이 경우 order by를 사용하지 않았지만 데이터는 ename으로 오름차순 정렬되어 나옵니다)

*** FULL : 인덱스 사용하지 않고 Table을 FULL Scan하라는 힌트

만약 성별처럼 "M" 아니면 "F"만 가지는 경우 분포도가 50%이므로 인덱스를 사용하는 것보다 인덱스를 사용하지 않는 경우가 성능에 효율적일 수 있습니다. 이러한 경우 사용하는 힌트죠...

사용 형식

/*+ FULL (table) */

select /*+ FULL(emp) */ ename, sal

from emp

where sex = 'M'

*** NO_INDEX : 기술된 테이블에 대해서는 인덱스를 사용하지 말라는 의미

select /*+ NO_INDEX(emp) */ ename, sal

from emp where sex = 'M';