[Oracle] 함수 기반 인덱스 생성 방법 (Funtion-Based Index)

오라클 SQL에서는 칼럼에 변형이 생기는 경우 인덱스를 사용하지 못한다. 그러나 함수 기반 인덱스(FBI)를 사용하면 인덱스를 사용하면 칼럼에 함수나 표현식을 사용해도 인덱스를 사용할 수 있도록 인덱스를 생성할 수 있다. 함수 기반 인덱스를 사용할 경우 조금 더 유연하게 인덱스를 사용할 수 있는 장점이 있지만, DML 작업 시 성능 저하가 발생할 수 있으므로 DML이 많은 테이블인 경우 신중히 사용하는 것이 좋다.

 

목차 함수 기반 인덱스 기본 개념 함수 기반 인덱스 생성 방법

 

함수 기반 인덱스 기본 개념

 SELECT /*+ index(a emp_ix01) */
        a.empno
      , a.ename
      , a.hiredate
      , a.sal
   FROM emp a
  WHERE TO_CHAR(a.hiredate, 'YYYY') = '1987'

 

 

위의 예시는 인덱스의 개념을 모를 때 많이 실수하는 부분이다. ( TO_CHAR(a.hiredate, 'YYYY') = '1987 )

 

조회할 칼럼에 함수나 표현식을 사용할 경우, 해당 칼럼에 인덱스가 존재해도 인덱스를 사용할 수 없다.

hiredate 칼럼에 emp_ix01 인덱스가 생성되어 있지만 인덱스를 타지 못하는 것을 확인할 수 있다.

 

 

조회할 칼럼에 TO_CHAR 함수를 사용하지 않고 BETWEEN 연산자를 사용하여 조회할 경우 정상적으로 인덱스를 타는 것을 확인할 수 있다.

 

함수 기반 인덱스 생성 방법

CREATE INDEX emp_ix02 ON emp(TO_CHAR(hiredate, 'YYYY'))

 

 

emp_ix02 함수 기반 인덱스를 생성한 후 정상적으로 인덱스를 타는 것을 확인할 수 있다.

 

CREATE INDEX emp_ix03 ON emp(NVL(sal, 0) + NVL(comm, 0))

 

 

단순 함수뿐만 아니라 복잡한 연산식을 인덱스로 생성할 수도 있다.

 

[Oracle] 오라클 인덱스 생성 및 삭제 방법