이화식 선생님의 새로쓴 대용량 데이터 베이스 1의 온라인 스터디 입니다.
매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.

진행기간: 2009.07 ~ 2009.10. 종료

2.3. 함수기반 인덱스(FBI, Fuction-Based Index)
2.3.1. 함수기반 인덱스의 개념 및 구조

  • 테이블의 컬럼들을 가공한 논리적 컬럼을 인덱스로 생성한것
    • (논리적컬럼- select-list나 조건절에 사용하는 모든 가공된 항목)
    • ex) select substr(bcode,4,2) from hrtest;
  • 함수기반 인덱스는 함수(Function)나 수식(Expression)으로 계산된 결과에 대해 B-Tree인덱스나 비트맵 인덱스를 생성
  • 함수는 우리가 흔희 사용하슨 산술식(Arithmetic expression), 사용자 지정 함수(User-defined Function),SQL의 제공(Built-in) 함수,패키지(Package)등
    • (sum,avg등의 그룹함수(Aggregate function)는 사용할수 없음
    • LOB나 REF타입으로 정의된 컬럼, 'TYPE'오브젝트가 테이블의 컬럼으로 지정되어 있는 네스티드(Nested)테이블 컬럼, 또는 이들을 포함 하고 있는 오브젝트 타입에서는 사용할수 없음)

2.3.2. 함수기반 인덱스의 제약사항


  • 비용기준 옵티마이져에서만 사용가능
  • 함수기반 인덱스를 생성한 후 반드시 통계정보를 생성
  • 사용자 지정함수는 반드시 'DETERMINISTIC'으로 선언
  • QUERY_REWRITE_ENABLED 파라메터가 TRUE로 선언
  • QUERY_REWRITE_INTEGRITY 가 TRUSTED로 선언
  • 사용자 권한(User Privilege)
  • 인덱스 생성 권한: Index Create / Any Index Create
  • 쿼리 재생성 권한: Query Rewrite/GLOBAL QUERY REWRITE
  • 함수나 수식의 결과가 NULL인 경우는 이 인덱스를 통해 액세스할 수 없음
  • 사용자 지정 함수를 사용한 경우에는 종속성(Dependency) 유지에 주의
  • 인덱스 정의에 사용된 사용자 함수가 재정의도었을 때 사용불가 (Disabled)상태가 됨( 9i이전)
  • 인덱스 소유자(Owner)의 실행(Execute)권한이 최소(Revoke)되면 사용불가 상태가 됨
  • 옵티마이져가 사용불가 (Disabled)상태가 된 인덱스를 선택하면 SQL의 실행은 실패
  • 이러한 경우에는 새로운 데이터가 입력,갱신, 삭제도 불가능해지므로 다음과 같은 명령을 사용하여 복구하여야 함
    • 'alter index ... enable / alter index .. rebuild' 를 이용하여 가용(enable)상태로 만들거나 재구축(Rebuild) 필요
    • 'alter index ... unusable'을 사용하여 미사용(Unusable)상태로 만듬
    • 사용을 하지 않는 상태이므로 무결성을 준수할 필요는 없음
    • 반드시 SKIP_UNUSABLE_INDEXES 파라메터가 TRUE로 지정이 되어 있어야 SQL이 실패하지 않음
    • 이상태를 사용가능 상태로 복구하기 위해서는 인덱스를 재구축하거나 삭제 후 재생성(Re-creation)하여야 함.
  • 스칼라 서브쿼리로 표현된 컬럼은 함수기반 인덱스를 생성할 수 없음
  • 값이 상황에 따라 달라질수 있는 SYSDATE,USER,ROWNUM등의 가상컬럼이 포함되면 이 인덱스를 생성할 수 없음
  • 파티션을 적용한 경우에 파티션 키를 함수기반 인덱스에 사용할 수 없음
  • 숫자 컬럼을 문자 연산 하거나 문자 컬럼을 수치연산하는 수식의 경우에는 직접기술수하지 않았더라가도 내부적으로 to_char,to_number가 추가되어 처리
  • 함수기반 인덱스에서는 NLS파라메터를 현재 기준으로 적용하기 때문에 만약 세션 레벨에서 재정의를 한다면 잘못된 결과를 얻을수 있으므로 주의
  • 그러나 NLS_SORT와 NLS_COMP는 세션 레벨의 정의에 영향을 받지 않음
  • where절에 기술된 컬럼이 표현된 것과 인덱스에 지정된 표현이 다르더라도 논리적으로 교환법칙이 성립하는 경우라면 인덱스는 사용가능
  • 함수기반 인덱스는 검색의 효율 향상을 위해서는 효과적이지만 구성 컬럼에 대한 빈번한 입력과 수정이 발생하면 부하가 가중되므로 주의
  • 사용자 지정함수
    • 사용자 지정함수에스는 거의 모든 절차형 처리뿐만 아니라 다른 테이블의 정보를 참조하여 가공할수가 있기 때문에 제약이 불가능
  • 사용자 함수를 재성성하면 과거버전에서는 'disabled'상태가 됨 (9i이전)
  • 10g이상에서는 사용가능 상태로 남음
    • 변경시점을 기준으로 과거의 자료 값을 인정
    • 사용자 지정함수에 다른 테이블을 참조했을 경우에도 동일적용
  • 참조된 테이블들에서 데이터가 입력되거나 갱신,삭제가 발생한다면 함수기반 인덱스는 영향을 받음
    • 참조하는 테이블의 데이터에 변화가 발생했더라도 함수기반 인덱스에 이미 저장된 과거의 데이터는 그대로 인정

2.3.3.함수 기반 인덱스의 활용

가.테이블 설계상의 문제를 해결
  • 컬럼의 중간 부분의 검색
    • 데이터 모델링에서 컬럼을 정의할 때 원자단위로 지정해야한다는 원칙
    • 생성시 자리수마다 의미를 부여한 컬럼(코드등)

인용:
SQL> select * from duri_dept where substr(rcode,1,4)='0716';

RCODE      RDESC                          RDATE
---------- ------------------------------ --------
0716045601 TEST14                         09/07/11
0716125602 TEST15                         09/07/11
0716085602 TEST16                         09/07/11


Execution Plan
----------------------------------------------------------
Plan hash value: 2142983186

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     3 |    99 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DURI_DEPT |     3 |    99 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SUBSTR("RCODE",1,4)='0716')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        642  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> create index dept_date_idx on duri_dept(substr(rcode,1,4));

인덱스가 생성되었습니다.

SQL> select * from duri_dept where substr(rcode,1,4)='0716';

RCODE      RDESC                          RDATE
---------- ------------------------------ --------
0716045601 TEST14                         09/07/11
0716125602 TEST15                         09/07/11
0716085602 TEST16                         09/07/11


Execution Plan
----------------------------------------------------------
Plan hash value: 3376368958

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |    99 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DURI_DEPT     |     3 |    99 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DEPT_DATE_IDX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SUBSTR("RCODE",1,4)='0716')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        642  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>



  • 조인 연결고리 컬림이 대응하지 않는 경우의 해결
인용:

SQL> insert into item_group
      select trim(to_char(rownum,'00')),trim(to_char(rownum/20,'00')),trim(to_char(rownum/5,'00')) from dual connect by level<100
      /

99 개의 행이 만들어졌습니다.

SQL> insert into items
  2  select class1||class2||class3,'연습'||rownum from item_group
  3  ;

99 개의 행이 만들어졌습니다.

SQL> set autotrace on;
SQL> select * from item_group x,items y
  2  where x.class1||x.class2||x.class3=y.group_cd and y.group_cd='670313';

CL CL CLA GROUP_C ITEM_DESC
-- -- --- ------- ----------
67 03 13  670313  연습67


Execution Plan
----------------------------------------------------------
Plan hash value: 3996579747

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    21 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |            |     1 |    21 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ITEM_GROUP |     1 |     9 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| ITEMS      |     1 |    12 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("Y"."GROUP_CD"="X"."CLASS1"||"X"."CLASS2"||"X"."CLASS3")
   2 - filter("X"."CLASS1"||"X"."CLASS2"||"X"."CLASS3"='670313')
   3 - filter("Y"."GROUP_CD"='670313')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

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

인덱스가 생성되었습니다.

SQL>  select * from item_group x,items y where x.class1||x.class2||x.class3=y.group_cd and y.group_cd='670313';

CL CL CLA GROUP_C ITEM_DESC
-- -- --- ------- ----------
67 03 13  670313  연습67


Execution Plan
----------------------------------------------------------
Plan hash value: 3578306337

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    21 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     1 |    21 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | ITEMS        |     1 |    12 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| ITEM_GROUP   |     1 |     9 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | GROUP_CD_IDX |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("Y"."GROUP_CD"='670313')
   4 - access("CLASS1"||"CLASS2"||"CLASS3"='670313')
       filter("Y"."GROUP_CD"="CLASS1"||"CLASS2"||"CLASS3")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         31  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed





  • 일자 컬림이 분할된 경우의 해결
인용:
where sal_yyyy||sal_mm||sal_dd >= '20051210' ;인덱스 사용불가

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



  • 데이터 타입이 상이한 조인 컬럼
인용:
create index deptno_idx on emp(to_number(deptno));


  • 조인 컬럼이 경우에 따라 달라지는 경우의 조인
'
인용:
''''
from sales s, departments d
where d.deptno=(case when sal_type=1 then sal_dept else agent_no end)
and d.location='BUSAN'
......

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


  • 부모 테이블의 컬럼과 결합한 인덱스 생성
인용:
처리범위를 줄일 수 있는 중요한 컬럼들이 서로 다른 테이블에 존재할 경우

insert into movement select trim(to_char(rownum,'000')),trim(to_char(rownum/5,'00000')),'기등' from dual connect by level <100;
insert into movement_trans select mov_order,trim(to_char(sysdate+level,'yyyymmdd')) from movement connect by level<5;



SQL> select * from movement x, movement_trans y where x.mov_order=y.mov_order
  2  and x.deptno='00017' and y.mov_date like '200907%';

MOV DEPTN ETC        MOV MOV_DATE
--- ----- ---------- --- --------
083 00017 기등       083 20090714
083 00017 기등       083 20090713
083 00017 기등       083 20090712
084 00017 기등       084 20090714
084 00017 기등       084 20090713
084 00017 기등       084 20090712
085 00017 기등       085 20090714
085 00017 기등       085 20090713
085 00017 기등       085 20090712
086 00017 기등       086 20090714
086 00017 기등       086 20090713

MOV DEPTN ETC        MOV MOV_DATE
--- ----- ---------- --- --------
086 00017 기등       086 20090712
087 00017 기등       087 20090714
087 00017 기등       087 20090713
087 00017 기등       087 20090712

15 개의 행이 선택되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 1818141208

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |    23 |  1441   (1)| 00:00:18 |
|*  1 |  HASH JOIN         |                |     1 |    23 |  1441   (1)| 00:00:18 |
|*  2 |   TABLE ACCESS FULL| MOVEMENT_TRANS |     1 |     9 |  1438   (1)| 00:00:18 |
|*  3 |   TABLE ACCESS FULL| MOVEMENT       |     5 |    70 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X"."MOV_ORDER"="Y"."MOV_ORDER")
   2 - filter("Y"."MOV_DATE" LIKE '200907%')
   3 - filter("X"."DEPTNO"='00017')


SQL> create or replace function get_deptno
  2  (  v_mov_order     in varchar2)
  3  return     varchar2        deterministic   is
  4  ret_val    varchar2(5);
  5  begin
  6     select deptno   into ret_val
  7     from movement
  8     where mov_order=v_mov_order;
  9     return  ret_val;
10  end        get_deptno;
11  /

함수가 생성되었습니다.


SQL> create index dep_date_idx on movement_trans(get_deptno(mov_order),mov_date);

인덱스가 생성되었습니다.

SQL> select * from movement x, movement_trans y where x.mov_order=y.mov_order and get_deptno(y.mov_order)='00017' and y.mov_date like '200907%';

MOV DEPTN ETC        MOV MOV_DATE
--- ----- ---------- --- --------
083 00017 기등       083 20090714
083 00017 기등       083 20090713
083 00017 기등       083 20090712
084 00017 기등       084 20090714
084 00017 기등       084 20090713
084 00017 기등       084 20090712
085 00017 기등       085 20090714
085 00017 기등       085 20090713
085 00017 기등       085 20090712
086 00017 기등       086 20090714
086 00017 기등       086 20090713

MOV DEPTN ETC        MOV MOV_DATE
--- ----- ---------- --- --------
086 00017 기등       086 20090712
087 00017 기등       087 20090714
087 00017 기등       087 20090713
087 00017 기등       087 20090712

15 개의 행이 선택되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 2125950770

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    23 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                   |                |     1 |    23 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MOVEMENT_TRANS |     1 |     9 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | DEP_DATE_IDX   |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | MOVEMENT       |    99 |  1386 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X"."MOV_ORDER"="Y"."MOV_ORDER")
   3 - access("DURIDBA"."GET_DEPTNO"("MOV_ORDER")='00017' AND "Y"."MOV_DATE" LIKE
              '200907%')
       filter("Y"."MOV_DATE" LIKE '200907%')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         53  recursive calls
          0  db block gets
        107  consistent gets
          1  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>



나. 오류 데이터의 검색 문재를 해결


  • 대소문자나 공백이 혼재된 컬럼의 검색
인용:
CREATE INDEX ename_upper_ix ON employees (UPPER(ename));
CREATE INDEX ename_upper_ix ON employees (UPPER(REPLACE(ename, ' '));


  • NULL 값을 치환하여 검색
인용:
CREATE INDEX end_date_idx ON account_history(NVL(end_date,'99991231'));

....
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));

select /*+    index_desc(x order_amount_idx) */*
from order_items x
where item_cd   = :b1
and rownum <=100;


  • 말일,단가,율의 검색
인용:
CREATE INDEX SAL_AMOUNT_IDX ON SALES (LAST_DAY(SAL_DATE),SAL_AMOUNT);

CREATE INDEX PRICE_IDX ON SALES (ROUND(SAL_AMOUNT/SAL_QUANTITY));


  • 기간,컬럼 길이 검색
인용:
CREATE INDEX TERM_IDX ON ACTIVITIES (expire_date - start_date);



라. 오브젝트 타입의 인덱스 검색

인용:
SQL> create type cube as object
  2  ( length number,
  3  width number,
  4  height number,
  5  member function volume return number deterministic
  6  );
  7  /

유형이 생성되었습니다.

SQL> create or replace type body cube as
  2     member function volume return number is
  3     begin
  4             return (length * width * height);
  5     end;
  6  end;
  7  /

유형 본문이 생성되었습니다.

SQL> create table cube_tab of cube;

테이블이 생성되었습니다.

SQL> create index volume_idx on cube_tab x (x.volume());

인덱스가 생성되었습니다.

SQL> desc cube_tab;
이름                                    널?     유형
----------------------------------------- -------- ----------------------------
LENGTH                                             NUMBER
WIDTH                                              NUMBER
HEIGHT                                             NUMBER

SQL> set autotrace on
SQL> select * from cube_tab x where x.volume() > 100;

선택된 레코드가 없습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 2688490031

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    39 |     1   (0)|00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUBE_TAB   |     1 |    39 |     1   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | VOLUME_IDX |     1 |       |     2   (0)|00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUBE"."VOLUME"("SYS_NC_ROWINFO$")>100)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        373  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


마. 배타적 관계의 인덱스 검색
  • 서로 다른 서브타입에 있는 컬럼들 중에서 동일한 속성으로 통합할 수 있는 컬럼들의 관계
  • 배타적 관계의 유일성 보장
인용:
CREATE UNIQUE INDEX official_id_idx on customers
    (CASE WHEN cust_type=1    THEN     resident_id    ELSE business_id END);

SELECT * FROM customers
WHERE (CASE WHEN CUST_TYPE=1 THEN resident_id ELSE business_id END) = :B1;


  • 유일성 채크
인용:
CREATE UNIQUE INDEX CONTRACT_IDX ON INSURANCE
    (CASE WHEN INS_TYPE='A01' THEN CUSTOMER_ID ELSE NULL END,
     CASE WHEN INS_TYPE ='A01' THEN INS_TYPE    ELSE NULL END);

'A01' 만 유일성 채크 나머지는 NULL


  • 배타적 관계의 결합 인덱스
인용:
CREATE INDEX order_delivery_idx1 ON order_delivery
( order_dept,
    CASE WHEN ORD_TYPE=1 THEN delivery_date ELSE SHIPPING_DATE END,
    ITEM_TYPE);



  • 함수 기반 인덱스는 버전에 따라 제약사항이 다를수 있음
  • 작성자: tofriend(남송휘)
  • 작성일: 2009년 07월 11일
  • 참고문헌 : 새로쓴 대용량데이터 베이스 1 - 이화식 , 기타 문서
  • 스터디 팀원은 최소 한가지 이상의 질문을 하여야하며  하루 한번 이상(출석)은 사이트에 접속 확인한다.