Oracle FBI란??

FUNCTION-BASED INDEX ( ORACLE 8I NEW FEATURE )
==============================================

Explanation
-----------

1. 개요

Function-based index는, 함수(function)이나 수식(expression)으로 계산
된 결과에 대해 인덱스를 생성하여 사용할 수 있는 기능을 제공한다.
질의 수행 시 해당 함수나 수식을 처리하여 결과를 가져 오는 것이 아니라,
인덱스 형태로 존재하는 미리 계산되어 있는 결과를 가지고 처리하므로
성능 향상을 기할 수 있다.

2. 제약사항

1) aggregate function 에 대한 function-based index 생성 불가.
(예 : sum(...) )

2) LOB, REF, nested table 컬럼에 대한 function-based index 생성 불가.

3. 주요 특징

1) cost-based optimizer에 의해 사용됨.
2) B*Tree / bitmap index로 생성 가능.
3) 산술식 (arithmetic expression), PLSQL function, SQL built-in
function 등에 적용 가능.

4) 함수나 수식으로 처리된 결과에 대한 range scan 가능
5) NLS SORT 지원
6) SELECT/DELETE를 할 때마다 함수나 수식의 결과를 계산하는 것이 아니라
INSERT/UPDATE 시 계산된 값을 인덱스에 저장.
7) 질의 속도 향상
8) object column이나 REF column에 대해서는 해당 object에 정의된
method에 대해 function-based index 생성 가능.

4. 생성 방법

CREATE [UNIQUE | BITMAP ] INDEX <index_name>
ON <tablename> (<index-expression-list>)

<index-expression-list> -> { <column_name> | <column_expression> }

예) CREATE INDEX EMP_NAME_INDEX ON EMP (UPPER(ENAME));
CREATE INDEX EMP_SAL_INDEX ON EMP( SAL + COMM, empno);

* Function-based index를 생성하기 위해서는 QUERY REWRITE 권한이
부여 되어 있어야만 한다.

예) GRANT QUERY REWRITE TO SCOTT;

5. Function-Based Index 사용을 위한 사전 작업

1) Function-based index는 cost based optimizer에서만 사용 가능하므로,
테이블에 대해 미리 analyze 해 주는 것이 바람직하다.
그리고 init 파일에서 OPTIMIZER_MODE 를 FIRST_ROWS 나 ALL_ROWS 등으
로 지정하거나 HINT 등을 사용하여 cost based optimizer가 사용되도록
한다.
2) init 파일에서 COMPATIBLE 파라미터 값을 8.1 이상으로 설정되어 있어야
한다.
( 예 : COMPATIBLE = 8.1.6 )
3) session/instance level 에서 QUERY_REWRITE_ENABLED 값이 TRUE 지정
되어 있어야 한다.
( 예 : ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; )

6. 예제

1) init 파라미터에서 다음과 같이 지정

compatible = 8.1.6 (반드시 8.1이상이어야 한다)
query_rewrite_enabled = true
query_rewrite_integrity = trusted

2) SCOTT 유저에서 function_based_index 생성

create index idx_emp_lower_ename
on emp
( lower(ename) ) ;

3) EMP table analyze

analyze table emp compute statistics ;

4) PLAN_TABLE 생성

@ ?/rdbms/admin/utlxplan.sql

5) Cost based optimizer 선택

alter session set optimizer_mode = FIRST_ROWS ;

6) Query 실행
explain plan set statement_id=''qry1'' FOR
select empno, ename
from emp
where lower(ename) = ''ford'' ;

7) PLAN 분석

SELECT LPAD('' '',2*level-2)||operation||'' ''||options||'' ''||object_name query_plan
FROM plan_table
WHERE statement_id=''qry1''
CONNECT BY prior id = parent_id
START WITH id = 0 order by id ;

-> 결과

QUERY_PLAN
------------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP
INDEX RANGE SCAN IDX_EMP_LOWER_ENAME

7. 결론

Function-based index는 적절하게 사용될 경우 성능상의 많은 이점을 가져
온다. Oracle8i Designing and Tuning for Performance에서도 가능한 한
Function-based index를 사용하는 것을 권장하고 있으며, LOWER(), UPPER()
등의 함수를 사용하여 불가피하게 FULL TABLE SCAN을 하는 경우에 대해서도
효과적으로 처리해 줄 수 있는 방안이라 할 수 있다.

출처 : 파란블로그 mystery님 블로그 http://blog.paran.com/testhan/228857