by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

인덱싱 테크닉




FUNCTION-BASED INDEX(FBI. ORACLE 8I NEW FEATURE)

개요

  • 개요
    1. 10g Standard Edition(10.1.0) 이상 부터 Function-based Index 기능 지원 가능
    2. 함수(function)이나 수식(expression)으로 계산된 결과에 대해 인덱스를 생성하여 사용할 수 있는 기능
    3. 인덱스 형태로 미리 계산되어 있는 결과를 가지고 처리하므로 성능 향상 기대
           쿼리 수행 시 해당 함수나 수식을 처리하여 결과 가져 오는 것 아님!!
    4. 인덱스 되어 있는 컬럼이라도 이들을 where조건에서 연산하면 기존의 인덱스 사용 NO!!
          즉, LOWER(), UPPER() 등의 함수 사용으로 FULL TABLE SCAN을 하는 경우에도 효과적으로 처리해 줄 수 있는 방법

  • 제약사항
    1. aggregate function(집계함수. ex) sum(...)) 에 대한 function-based index 생성 불가
    2. LOB, REF, nested table 컬럼에 대한 function-based index 생성 불가

  • 특징
    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에 정의된 방법에 대해 function-based index 생성 가능
    9. FBI를 생성하기 위해서는 QUERY REWRITE 권한이 부여 되어 있어야 함

  • 생성 방법
    1. FBI는 CBO에서만 사용 가능하므로, 사전 작업
      1. 해당 테이블을 미리 analyze 한다
      2. instance level : init 파일에 【 OPTIMIZER_MODE = FIRST_ROWS || ALL_ROWS 】 지정
      3. SQL level : HINT 사용
      4. COMPATIBLE 설정 : init 파일에서 COMPATIBLE = 8.1 이상
            ex) COMPATIBLE = 10.2.0.0.0
      5. QUERY_REWRITE_ENABLED 설정 : 【 QUERY_REWRITE_ENABLED = true 】로 설정(session/instance level)
            ex) ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

        • 문법
          CREATE BITMAP INDEX <index_name> ON <tablename> (<index-expression-list>)
          <index-expression-list> -> 【 <column_name> | <column_expression> 】

        • 예제1
          GRANT QUERY REWRITE TO SCOTT;
          CREATE INDEX EMP_NAME_INDEX ON EMP (UPPER(ENAME));
          CREATE INDEX EMP_SAL_INDEX ON EMP( SAL + COMM, empno);
          CREATE INDEX sales_margin_idx ON sales(revenue - cost) ;

        • 예제2
          ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE ;
          SELECT ordid FROM sales WHERE (revenue - cost) > 1000 ;
          ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE ;



    2. 확인
    • DBA_INDEXES 또는 USER_INDEXES에서 【 FUNCIDX_STATUS = ENABLED 】 되어 있으면 FBI 임
      select index_name, table_name, FUNCIDX_STATUS from user_indexes;
      
      SELECT INDEX_NAME, COLUMN_NAME, DESCEND
      FROM DBA_IND_COLUMNS
      WHERE INDEX_OWNER = 'SCOTT';
      



테스트

  • 테스트 전 확인사항
    1. init 파라미터 설정
      1. 기준 값
      • compatible = 10.2.0.1.0(반드시 8.1이상으로 설정)
      • query_rewrite_enabled = true
      • query_rewrite_integrity = trusted
        conn scott/loveora
        show parameter query_rewrite
        
        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- ----------
        query_rewrite_enabled                string      TRUE
        query_rewrite_integrity              string      enforced
        
        show parameter comp
        
        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- -------------------------
        compatible                           string      10.2.0.1.0
        nls_comp                             string
        plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
        plsql_v2_compatibility               boolean     FALSE
        
        alter session set query_rewrite_integrity=trusted;
        Session altered.
        



    2. 테이블 생성
      create table t as select 'Y' processed_flag, a.* from all_objects a;
      
      Table created.
      



    3. view 생성
      create or replace view v
        as
        select t.*,
        case when processed_flag='N' then 'N'
        else NULL
        end processed_flag_indexed
        from t;
      
      View created.
      



    4. 인덱스 생성 및 분석
      create index t_idx on
        t(case when processed_flag = 'N' then 'N'
        else NULL
        end);
      
      Index created.
      
      analyze index t_idx validate structure;
      
      Index analyzed.
      
      • 분석 결과 확인하기 : 최초 인덱스에는 아무것도 없음
        SQL> col name for a10
        SQL> col del_lf_rorws for 999
        SQL> col lf_rows for 99999
        SQL> col lf_blks for 99999
        SQL> select name, del_lf_rows,lf_rows,lf_blks from index_stats;
        
        NAME       DEL_LF_ROWS LF_ROWS LF_BLKS                                          
        ---------- ----------- ------- -------                                          
        T_IDX                0       0       1                                          
        
      • 데이터 수정과 인덱스 변화
        • processed_flag 컬럼을 100개 update 하면, 인덱스 엔트리(LF_ROWS)는 100개 생성됨
          SQL> update t set processed_flag='N' where rownum <= 100;
          
          100 rows updated.
          
          SQL> analyze index t_idx validate structure;
          
          Index analyzed.
          
          SQL> select name, del_lf_rows,lf_rows,lf_blks from index_stats;
          
          NAME       DEL_LF_ROWS LF_ROWS LF_BLKS                                          
          ---------- ----------- ------- -------                                          
          T_IDX                0     100       1                                          
          



    5. FBI 사용을 위한 테이블 분석
      analyze table t compute statistics
        for table
        for all indexes
        for all indexed columns
      SQL> /
      
      Table analyzed.
      
      column rowid new_val r
      select rowid,object_name from v
        where processed_flag_indexed='N' and rownum=1;
      
      ROWID              OBJECT_NAME                                                  
      ------------------ ------------------------------                               
      AAAM+vAAEAAAxkkAAA DUAL                                                         
      
    6. processed_flag의 수정 후 바로 trace 결과 확인
      SQL> update v.processed_flag = 'Y'
        2  set processed_flag='Y'
        3  where rowid='&R';
      old   3: where rowid='&R'
      new   3: where rowid='AAAM+vAAEAAAxkkAAA'
      
      1 row updated.
      



    7. autotrace 확인
      set timing on
      set time on
      set autotrace on
      select rowid, object_name
         from v
         where processed_flag_indexed='N'
         and rownum=1;
      
      ROWID              OBJECT_NAME                                                  
      ------------------ ------------------------------                               
      AAAM+vAAEAAAxkkAAB DUAL                                                         
      
      Elapsed: 00:00:00.03
                                                                                      
      --------------------------------------------------------------------------------------
      | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                
      --------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |       |     1 |    32 |     2   (0)| 00:00:01 |                                                                                
      |*  1 |  COUNT STOPKEY               |       |       |       |            |          |                                                                                
      |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    32 |     2   (0)| 00:00:01 |                                                                                
      |*  3 |    INDEX RANGE SCAN          | T_IDX |    99 |       |     1   (0)| 00:00:01 |                                                                                
      --------------------------------------------------------------------------------------
      


      FIG 1. 시간에 따라 인덱스 엔트리가 변하므로 실제로 쿼리마다 여러번의 I/O 필요

    8. processed_flag 수정 & 인덱스 분석 후 trace 결과 확인
      update v  set processed_flag='Y'  where rowid='&R';
      old   3: where rowid='&R'
      new   3: where rowid='AAAM+vAAEAAAxkkAAB'
      
      1 row updated.
      
      analyze index t_idx validate structure;
      
      Index analyzed.
      
      select name,del_lf_rows,lf_rows,lf_blks from index_stats;
      
      NAME       DEL_LF_ROWS LF_ROWS LF_BLKS                                          
      ---------- ----------- ------- -------                                          
      T_IDX                2     100       1                                          
      
      • 100개(LF_ROWS) 중에서 2개(DEL_LF_ROWS)는 삭제되어 재사용 가능

    9. 다른 세션에서 데이터 삽입
      insert into t
       select 'N' processed_flag, a.* from all_objects a
       where rownum <=2;
      
      2 rows created.
      
      analyze index t_idx validate structure;
      
      Index analyzed.
      
      select name,del_lf_rows,lf_rows,lf_blks from index_stats;
      
      NAME       DEL_LF_ROWS LF_ROWS LF_BLKS                                          
      ---------- ----------- ------- -------                                          
      T_IDX                0     100       1                                          
      
      set autotrace on
      select rowid, object_name from v where processed_flag_indexed='N' and rownum=1;
      
      ROWID              OBJECT_NAME                                                  
      ------------------ ------------------------------                               
      AAAM+vAAEAAAxkkAAC SYSTEM_PRIVILEGE_MAP                                         
      



  • unique index
    1. 테스트 테이블 및 인덱스 생성
      create table project
      (project_ID number primary key, teamid number,
        job varchar2(100), status varchar2(20)
        check(status in ('ACTIVE','INACTIVE')));
      
      Table created.
      
      create unique index
        job_unique_in_teamid on project
        (case when status = 'ACTIVE' then teamid else null end,
        case when status = 'ACTIVE' then job else null end)
      /
      
      Index created.
      



    2. unique 제약조건에 위배
      insert into project(project_id,teamid,job,status) values(1,10,'a','ACTIVE');
      
      1 row created.
      
      insert into project(project_id,teamid,job,status) values(2,10,'a','ACTIVE');
      
      insert into project(project_id,teamid,job,status) values(2,10,'a','ACTIVE')
      *
      ERROR at line 1:
      ORA-00001: unique constraint (SCOTT.JOB_UNIQUE_IN_TEAMID) violated 
      
      update project set status = 'INACTIVE'
        where project_id=1
        and teamid=10 and status='ACTIVE';
      
      1 row updated.
      
      insert into project(project_id,teamid,job,status) values(2,10,'a','ACTIVE');
      
      1 row created.
      



  • Plan table에서 실행계획 확인하기
    create index idx_emp_lower_ename on emp ( lower(ename) ) ;
    
    Index created.
    
    analyze table emp compute statistics ;
    
    Table analyzed.
    
    1. optimizer_mode 확인
      show parameter optimizer_mode
            NAME        VALUE
      =============== ==========
      optimizer_mode   ALL_ROWS
      
      if) optimizer_mode가 ALL_ROWS or FIRST_ROWS로 설정되어 있지 않을 경우 
      alter session set optimizer_mode = FIRST_ROWS;
      alter session set optimizer_mode = ALL_ROWS;
      
    2. PLAN Table 만들기
      @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql
      
    3. Query 실행
      explain plan set statement_id='qry1' FOR 
      select empno, ename from emp where lower(ename) = 'ford' ;
      
    4. 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                                        
      



    5. view 조회시 실행계획 보기
      explain plan set statement_id='qry2' FOR 
       select rowid, object_name from v where processed_flag_indexed='N' and rownum=1
      
      Explained.
      
      SELECT LPAD(' ',2*level-2)||operation||' '||options||' '||object_name query_plan
        FROM plan_table
        WHERE statement_id='qry2'
        CONNECT BY prior id = parent_id
        START WITH id = 0 order by id
      
      QUERY_PLAN                                                                      
      --------------------------------------------------------------------------------
      SELECT STATEMENT                                                                
        COUNT STOPKEY                                                                 
        COUNT STOPKEY                                                                 
          TABLE ACCESS BY INDEX ROWID T                                               
          TABLE ACCESS BY INDEX ROWID T                                               
          TABLE ACCESS BY INDEX ROWID T                                               
          TABLE ACCESS BY INDEX ROWID T                                               
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
            INDEX RANGE SCAN T_IDX                                                    
      
      15 rows selected.
      



Reference Documents

1. OTN 포럼 : FUNCTION-BASED INDEX( ORACLE 8I NEW FEATURE )

문서에 대하여

  • 최초작성자 : 박혜은
  • 최초작성일 : 2009년 11월 27일
  • 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.