메뉴 건너뛰기

bysql.net

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)는 이름에서 나오듯이 '함수'나 '수식'을 기반으로 생성된 인덱스이다.

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


번호 제목 글쓴이 날짜 조회 수
35 Front Page file 운영자 2010.09.06 164252
34 3.1. SQL과 옵티마이져 (3/3) (3.1.3 ~ 3.1.5) [2] file 실천하자 2010.10.08 26956
33 4.1. 인덱스의 선정 기준 (1/2) (4.1.1.~4.1.5.) [2] file 노랑배 2010.11.13 23369
32 2.2. 비트맵(Bitmap) 인덱스 file 실천하자 2010.10.04 20925
31 1.4. 부분범위처리로의 유도(1/2) (1.4.1 ~ 1.4.8) file 실천하자 2010.11.26 19346
» 2.3. 함수기반 인덱스(FBI, Function-Based Index) pranludi 2010.10.01 18677
29 4.1. 인덱스의 선정 기준 (2/2) (4.1.6.) [1] file 실천하자 2010.11.15 16803
28 2.3.조인 종류별 특징 및 활용방안 (3/4) (2.3.4~2.3.5) 실천하자 2010.12.08 15346
27 1.4. 부분범위처리로의 유도(1/2) (1.4.9) 실천하자 2010.12.08 14430
26 1.1. 테이블과 인덱스의 분리형 file 실천하자 2010.09.13 13905
25 3.1. SQL과 옵티마이져 (2/3) (3.1.2.3 ~ 3.1.2.5) 노랑배 2010.10.06 13719
24 2.1. B-tree 인덱스 file 김진희 2010.10.04 13017
23 3.2.4. 비트맵(Bitmap) 실행계획 file 실천하자 2010.11.08 12832
22 2.3.조인 종류별 특징 및 활용방안 (2/4) (2.3.2~2.3.3) file 실천하자 2010.12.03 12561
21 제1장. 부분범위처리(Partial range scan) file supersally 2010.12.01 12321
20 2.1.조인과 반복연결(loop query)의 비교 pranludi 2010.12.05 11447
19 3.2.5. 기타 특수한 목적을 처리하는 실행계획 pranludi 2010.11.09 11331
18 3.3. 실행계획의 제어 file supersally 2010.11.08 11130
17 3.2.1. 스캔(Scan)의 기본유형 [1] pranludi 2010.10.19 11072
16 3.2.3. 연산 방식에 따른 실행계획 노랑배 2010.10.18 10897