2.3. 함수기반 인덱스 ( FBI, Function-Based Index )

1. 함수기반 인덱스?

우리가 사용하는 컬럼은 물리적으로 존재하는 실제 컬럼만 아닌 가공된 항목도 모두 논리적인 컬럼이다.

함수기반인덱스(FBI)는 이 논리적인 컬럼을 인덱스로 생성한 것.

2. 생성 조건

일반 함수나 수식으로 계산된 결과에 대해 B-Tree, Bitmap 인덱스로 생성할 수 있다.

사용가능한 함수 : 산술식, 사용자 지정 함수, SQL의 제공 함수, 패키지

사용불가능 함수 : SUM, AVG등의 그룹 함수

                  LOB나 REF타입으로 정의된 컬럼

                  'TYPE' 오브젝트가 테이블의 컬럼으로 지정되어 있는 네스티드(Nested) 테이블 컬럼

3. 제약 사항

• 비용기준 옵티마이져에서만 사용 가능하다.
• FBI 생성 후 반드시 통계정보를 생성
• 반드시 'DETERMINISTIC'으로 선언
• Query_Rewrite_Enabled 파라메터가 True로 선언
• Query_Rewrite_Integrity가 Trusted로 선언
• 인덱스 생성 권한(INDEX CREATE, ANY INDEX CREATE), 쿼리 재생성 권한(QUERY REWRITE, GLOBAL QUERY REWRITE) 권한 필요
• 함수나 수식의 결과가 NULL인 경우는 인덱스를 통해 액세스 할 수 없다.
• SYSDATE, USER, ROWNUM 등의 가상컬럼으로 생성할 수 없다.
• 숫자 컬럼을 문자연산 하거나 문자 컬럼을 수치연산하는 수식의 경우에는 직접 기술하지 않았더라도 내부적으로 TO_CHAR, TO_NUMBER 가 추가되어 처리된다.
• 구성 컬럼에 대한 빈번한 입력과 수정이 발생하면 부하가 가중되므로 주의할 필요가 있다.

4. 활용

• 테이블 설계상의 문제를 해결

 - 컬럼의 중간 부분의 검색

   create index from_loc_idx on orders (substr(ship_id, 5, 3) );

 - 조인 연결고리 컬럼이 대응하지 않은 경우의 해결

   create index group_cd_idx on item_group ( class1||class2||class3 );

   select ...

     from item_group x, items y

    where x.class1||x.class2||x.class3 = y.group_cd

   ...

 - 일자 컬럼이 분할된 경우의 해결

   create index sal_date_idx on sales (sal_yyyy||sal_mm||sal_dd);

   ...

   where sal_yyyy||sal_mm||sal_dd >= '20051210'

 - 데이터 타입이 상이한 조인 컬럼

   create index deptno_idx on emp ( to_number(deptno) );

 - 조인 컬럼이 경우에 따라 달라지는 경우의 조인

   create index deptno_idx on sales ( case when sal_type = 1 then sal_dept else agent_no end);

   ...

   from sales s, departments d

   where d.deptno = (case when sal_type = 1 then sal_dept else agent_no end)

     and d.location = 'PUSAN'

    ....

 - 부모 테이블의 컬럼과 결합한 인덱스 생성

• 오류 데이터의 검색 문제를 해결

 - 대.소문자나 공백이 혼재된 컬럼의 검색

   create index ename_upper_idx on employees (upper(ename));

   create index ename_upper_idx on employees (upper(replace(ename, ' '));

 - NULL 값을 치환하여 검색

   create index end_date_idx on account_history ( nvl(end_date, '99991231') );

   create index start_end_idx on account_history ( nvl(end_date, '99991231'), start_date );

   ....

   where :input_date between start_date and nvl(end_date, '99991231');

 - 접두사(Prefix)를 채워서 검색

   create index call_number_idx on call_date ( decode(substr(call_number, 1, 3), '018', '', '016')||call_number);

• 가공처리 결과의 검색

 - 복잡한 계산 결과의 검색

   create index order_amount_idx on order_items

      ( item_cd, (order_price - nvl(order_discount, 0)) * order_count));

 - 말일, 단가, 율의 검색

   create index sal_amount_idx on sales ( last_day(sal_date) , sal_amount );

 - 기간, 컬럼 길이 검색

   create index term_idx on activities (expire_date - start_date );

• 오브젝트 타입의 인덱스 검색
• 배타적 관계의 인덱스 검색

 - 배타적 관계의 유일성 보장

   create unique index official_id_idx on customers ( case when cust_type = 1 then resident_id else business_id end);

 - 배타적 관계의 결합 인덱스

==>

함수기반인덱스(FBI)는 이름에서 나오듯이 '함수'나 '수식'을 기반으로 생성된 인덱스이다.

컬럼을 함수나 수식으로 가공한 값을 기반으로 인덱스를 생성하여 데이타의 접근성을 효율적으로 해준다.