오라클인덱스,함수기반인덱스(Function Based Index)
인덱스(Function Based Index)
SQL 문장의 WHERE 절에 SQL함수(SQL Function)가 사용되는 경우 인덱스 컬럼에 변형이 생기는 경우이므로 인덱스를 사용하지 못하는 경우가 있습니다. 이러한 경우를 해결할 수 있는 것이 함수 기반 인덱스인데...
우선 아래의 예문을 보자.
(EMP 테이블의 ENAME 컬럼에 대해 인덱스가 걸려 있다고 가정)
SQL>SELECT ENAME, SAL
FROM EMP
WHERE SUBSTR(ENAME,0,1) = '김';
물론 위의 경우 WHERE ENAME LIKE '김%' 라고 하는 것이 바람직하겠지만 인덱스 컬럼에 대해 변형을 가하는 예문이라고 생각하자. 위 경우 처럼 인덱스가 존재하는 컬럼에 대해 SQL함수를 사용하게 되면 인덱스 칼럼에 변형이 생겨 ENAME에 대해 생성되어 있는 인덱스를 사용하지 못합니다. 이러한 경우 함수 기반 인덱스(Function Based Index)를 사용하면 해결할 수 있는데 다음처럼 인덱스를 만듭니다. 또한 인덱스가 생성될 컬럼에는 SQL함수나 산술식, 상수, 사용자 정의 함수를 적용할 수 있습니다.
SQL>create indexidx_emp_ename on emp(substr(ename, 0, 1));
이러한 함수 기반 인덱스의 경우 다음과 같은 장단점이 있는데...
장점이라면 당연히 인덱스 컬럼에 SQL함수 등을 적용하여 변형을 가하더라도 함수가 적용된 데이터 전체를 인덱스를 사용하여 빠른 Access를 보장하는 것인데 DML사용시 함수를 사용하여 인덱스 값을 저장해야 하므로 overhead가 생길 수 있으며 인덱스 사용에 있어서도 WHERE절에 함수 기반 인덱스가 적용된 SQL함수가 있는 경우에만 인덱스가 사용 가능하며 다른 조건들에 대해서는 인덱스를 사용할 수 없습니다. 즉 위의 경우 ename like '김%', ename = '홍길동'과 같은 경우에는 인덱스를 사용할 수 없다는 것이다.
또한 함수기반 인덱스(Function Based Index)를 생성하면 기본적으로 bitmap 인덱스가 생성되며 인덱스 생성은 system 권한이 있어야 가능합니다.. 이 함수기반 인덱스가 생성하기에 제일 까다로운데 Oracle parameter중 compatible 값이 8.1.0 이상으로 되어 있어야 하며 QUERY_REWRITE_ENABLE parameter 값이 TRUE, QUERY_REWRITE_INTEGRITY parameter가 TRUSTED로 되어 있어야 하고 해당 ORACLE USER가 QUERY REWRITE 권한이 있어야 한다.
SQL>conn / as sysdba
Sql문의 실행 경로를 추적할 수 있는 권한을 부여하기 위한 롤을 생성하기 스크립트 실행
SQL> @D:\app\onj\product\11.2.0\dbhome_1\sqlplus\admin\ plustrce.sql
Sql문의 실행 경로를 기록할 plan_table을 생성하기 위해 utlxplan.sql 스크립트 실행
SQL> @D:\app\onj\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql
SQL>grant plustrace to scott;
SQL>alter session set QUERY_REWRITE_ENABLED = TRUE;
SQL>alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
SQL>grant query rewrite to scott;
SQL>conn scott/tiger
SQL>
SQL>create index fidx_emp_ename on emp(substr(ename, 0, 1));
SQL>select * from emp where substr(ename, 0,1) = 'S';
fidx_emp_ename 인덱스를 range scan 하는 것을 볼 수 있을 것이다.
