1. 2.3.1 함수기반 인덱스의 개념 및 구조
2. 2.3.2 함수기반 인덱스의 제약사항
3. 2.3.3 함수기반 인덱스의 활용

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)