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

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




2.3.1 함수기반 인덱스의 개념 및 구조

개념

  • SELECT-List나 조건절에 사용하는 모든 가공된 항목을 인덱스로 생성한다.
  • 쿼리의 결과(SELECT * FROM PROD WHERE CNT * PROD = 300)를 새로운 테이블로 생성했다고 가정한다.
  • nameing rule는 'SYS_NC00013$' 로 만들어진다.
  • 함수(Function (사용자 지정 함수, sql의제공함수, 패키지 등))나 수식(Expression)으로 계산된 결과에 대해 B-Tree인덱스나 비트맵 인덱스를 생성할수 있다.
  • 오브젝트 타입(Object Type)들은 해당 컬럼에 정의된 매소드 통해서만 적용가능하다.

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

  • SUM, AVG등 그룹함수는 제외다 (Reason: 테이블의 로우단위가 아닌 새롭게 생성된 논리적 로우 단위로 적용하기 때문이다.).
  • LOB, REF 타입으로 정의된 커럼 'TYPE'오브젝트가 테이블의 컬럼으로 지정되어 있는 네스티드 데이블컬럼은 사용할수없다.
  • CBO(비용기준옵티마이져)에서만 사용가능.
  • 인덱스 생성 후 반드시 통계정보를 생성해야된다.
  • 사용자 지정함수는 반드시 DETERMINISTIC 으로 선언(입력값이 같으면 리턴되는 결과 값도 항상 같음을 보장하는 함수.)
  • 사용자 지정함수를 사용한 경우에는 종속성 유지에 주위해야한다.
  • 함수나 수식의 결과가 NULL인경우는 인덱스를 통해 액세스가 불가능하다.
  • 소유자의 실행권한이 취소 된면 사용불가 상태가 되버린다.(enable, rebuild 해야한다.)
  • 스칼라 서브쿼리로는 인덱스 사용할수 없다.
  • 값이 상황에따라 달라질 수 있는 SYSDATE, USER, ROWNUM등의 가상컬럼이 포함되면 인덱스생성할수 없다.
  • 파티션 키로는 인데스 사용할수 없다.
  • 숫자 컬럼을 문자 연산하거나 문자컬럼을 수치연산하는 수식의 경우에는 직접 기술하지 않더라도 내부적으로 알아서 TO_CHAR, TO_NUMBER가 추가 처리된다.

    파라미터 사용

  • alter session set QUERY_REWRITE_ENABLE parameter = 'true';
  • alter session set QUERY_REWRITE_INTEGRITY parameter = 'trusted';

권한 사용

  • 인덱스 생성권한 : INDEX CREATE / ANY INDEX CREATE
  • 쿼리 재 생성권한 : QUERY REWRITE / GLOBAL QUERY REWRIE
  • grant query rewrite to 유저명;

2.3.3 함수기반 인덱스의 활용

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

컬럼의 중간 부분의 검색

  • 함수기반으로 만든 INDEX : CREATE INDEX from_loc_idx ON orders (SUBSTR(sip_id,5,3));
  • 자주 사용되는 다른컬럼과결합한 INDEX : CREATE INDEX repair_ord_idx ON orders (SUBSTR(sip_id,5,3), ord_date);

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

  • 데이터가 적은 상위테이블에 함수기반 index생성
  • CREATE INDEX group_cd_idx ON item_grop (column1||column2||column3);

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

  • where sal_yyyy ||sal_mm ||sal_dd > = '20110803' 이경우에 인덱스 테워야할경우.
  • CREATE INDEX sal_date_idx ON sales (sal_yyyy ||sal_mm ||sal_dd); <-로 사용하는데 용이하다..
  • 그치만 요즘은 이렇게 않하고 where sal_ymd >= '20110803' 자주 사용한다.

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

  • DEPT 테이블의 DEPTNO가 NUMBER이고 EMP 테이블의 DEPTNO가 VARCHAR2일때
  • CREATE INDEX deptno_idx ON emp (TO_NUMBER(deptno)); <-이렇게 사용한다. 그냥 query날릴때 형변환해서 쓰는게 낮을꺼같다.

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

  • deparment테이블이 조인하는 컬럼과 달라질때에 sales테이블 과 departments 에 deparments가 먼저 수행될때.
  • ...... from sales s, deparments d where d.deptno = CASE when sal_type =1 THEN sal_dept ELSE agent_no END) and d.location ='PUSAN'... 일때
  • CREATE INDEX deptno_idx ON sales (CASE WHEN sal_type =1 THEN sal_dept DLSE agent_no END);

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

    
               - from movement x, movement_trans y where x.mov_order =y.mov_order and x.deptno = '12345'(<- 처리주관 조건) and y.mov_date like '20110%' (<- 체크기능 역할) 
               -  이들 두 조건이 처리주관조건으로 된다면 수행속도를 얻을수있다.
               create or replace function get_deptno(v_mov_order in number)
               return varchar2 DETERMINISTIC is
               ret_val varchar2(5);
               begin 
                   select deptno into ret_val
                     from movement
                    where mov_order = v_mov_order;
                 return ret_val;
               end get_deptno;
    
     create index dept_date_idx on movement_trans(get_deptno(mov_order),mov_date);
              - x.deptno => get_deptno(mov_order)데체하면 둘다 처리주관조건이 될수있어 수행속도가 향상된다.
           

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

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

  • enmae컬럼에 성 과 명사이에 공백이 있거나 없을경우.
  • =>CREATE INDEX ename_upper_ix ON employees (UPPER(ename));
  • 불필요한 공백을 제거하고 검색하려고 할경우.
  • => CREATE INDEX ename_upper_ix ON employees (UPPER(REPLACE(ename, ' ')));

    NULL값을 치환하여 검색

  • NULL값 을 다른 값으로 치환하여 검색
  • CREATE INDEX end_date_idx ON account_history (NVL(end_date, '99991231'));

    접두사를 채워서 검색

  • 식별번호가 다른 전화번호가 발생할때 과거데이터를 수정하기 어려울경우.
             CREATE INDEX call_number_idx ON call_data (DECODE(SUBSTR(call_number,1,3),'011','','010')||call_number);
            

다. 가공처리 결과의 검색

복잡한 계산 결과의 검색

          - create index order_amount_idx on order_items(item_cd, (order_price-nvl(order_discount,0),0) * order_count));
            select /*+ index_desc(x order_amount_idx) */ *
              from order_items x
             where item_cd = :b1
               and rownum <= 100

           Execution Plan
           ----------------------------------------
           SELECT STATEMENT Optimizer = FIRST_ROWS
           COUNT (STOPKEY)
             TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_ITEMS'
               INDEX (RANGE SCAN {*DESCENDING*}) OF 'ORDER_AMOUNT_IDX'
         

말일, 단가,율 의 검색

  • sal_date달에 가장높은 sal_amount을 검색할경우.
  • CREATE INDEX sal_amount_idx ON sales (LAST_DAY(sal_date), sal_amount);
  • 판매단가를 검색하는 경우 증가율, 할당도 검색 할경우.
  • CREATE INDEX price_idx ON sales (ROUND(sal_amount/sal_quantity));

    기간, 컬럼 길이 검색

  • DATE 타입의 업무처리 기간검색
  • CREATE INDEX term_idx ON activities (expire_date - start_date);
  • 업무처리 기간검색 (길이)
     - CREATE INDEX source_length_idx ON print_media(text_length(source_text));  

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

  • CUBE 형(TYPE)으로된 테이블생성후 VOLUMN()메소드로 인덱스 생성.
        CREATE TALBLE cube_tab OF CUBE;
        CREATE INDEX volume_idx ON cube_tab x (x.volume());
    
        - 인덱스를 경유해서 액세스 수행
        SELECT * FROM cube_tab x WHERE x.volume() > 100;
    

마. 배타적 관계의 인데스 검색

배타적 관게의 유일성 보자

  • 특정범위의 데이터 ins_type = 'A01'인 경우만 customer_id와 ins_type 두컬럼에 유일성체크하는 경우.
    CREATE UNIQUE INDEX contract_idx ON insurance
    ( CASE WHEN THAN ins_type = 'A01' THEN customer_id ELSE null END,
    CASE WHEN THAN ins_type = 'A01' THEN ins_type ELSE null END
    );

    배타적 관계의 결합 인덱스

  • 적용일기준일이 다른 컬럼들과 결합할 경우.
    CREATE INDEX order_deliver_idx1 ON order_delivery
    (order_dept, -> 고정된 컬럼
    CASE WHEN ord_type=1 THEN delivery_date ELSE shipping_date END), Item_type)

문서정보

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