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)