테이블의 컬럼들을 가공한 값으로 인덱스로 생성한 것이다.
함수기반 인덱스를 생성하면 오라클서버가 생성한 인덱스컬럼을 확인할 수 있다.
create index idx_emp_salyear on emp(sal*12);
;
select index_name,column_name
from user_ind_columns
where table_name='EMP';
;
index_name column_name
==================================
IDX_EMP_SALYEAR SYS_NC00009$
SQL>alter session set QUERY_REWRITE_ENABLE = TRUE;
SQL>alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
SQL>grant query rewrite to scott;
사용가능한 형식은 테이블의 열, 상수, sql함수 및 사용자가 정의한 함수이다.
create index from_loc_idx on orders (substr(ship_id,5,3));
create index repair_loc_idx on orders (substr(ship_id,3,2), ord_date);
select ...
from item_group x, items y
where x.class1 || x.class2 || x.class3 = y.group_cd
또는
select ...
from item_group x, items y
where x.class1 = substr(y.group_cd,1,2)
and x.class2 = substr(y.group_cd,3,2)
and x.class3 = substr(y.group_cd,5,3)
위와 같은 경우 다음과 같은 함수기반 인덱스로 해결할 수 있다.
create index group_cd_idx on item_group(x.class1 || x.class2 || x.class3 );
create index sal_date_idx on sales (sal_yyyy || sal_mm || sal_dd);
create index deptno_idx on emp (to_number(deptno));
select ...
from sales s, departments 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
else
agent_no
end);
select ...
from movement x, movement_trans y
where x.mov_order = y.mov_order
and x.deptno = '12310'
and y.mov_date like '200512%'
위와 같은 sql이 있다면 x.deptno = '12310'이 처리 주관 조건이 된다면 move_date like '200512%'는 체크기능의 역할만 하게 된다
이때 두개의 조건이 함께 처리 주관조건이 된다면 아주 양호한 수행속도를 얻을 수 있다면 다음과 같은 함수기반인덱스를 이용하여 해결 할 수 있다.
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);
단 함수내에 참조 테이블이 있을 경우 버전 별로 차이가 있으므로 주의해서 사용해야한다.
?
create index ename_upper_idx on employees (upper (ename));
create index ename_trim_idx on employees(replace(ename,' '));
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),'018','','016')||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
create index sal_amount_idx on sales (last_day(sal_date), sal_amount);
create index term_idx on activities (expire_date - start_date)
-- 길이를 검색하는 경우
create index source_length_idx on print_media(text_length(source_text));
육면체(cube)를 오브젝트타입으로 생성하오 이것을 이용하여 부피를 구하는 예
create type cube AS object
(
length number,
width number,
heigth number,
member function volume return number DETERMINISTIC
);
create or replace type body cube as
member function volume return number is
begin
return (length * width * height);
end;
end;
create table cube_tab of cube;
create index volume_idx on cube_tab x (x.volume());
select * from cube_tab x where x.volume() > 100
insert into cube_tab values (cube(100,1,2));
검색 시 customers 테이블의 cust_type에 따라 사업자번호 또는 주민번호의 내용을 조회한다.
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;
유일성을 체크하는 방법 ins_type이 'A01'인 경우만 customer_id와 ins_type 두컬럼에 대한 유일성을 체크한다.
create unique index contract_idx on insurance (
case when then ins_type = 'A01' then customer_id else null end,
case when then ins_type = 'A01' then ins_type else null end
);
insert into contact_person (insuran_id, ..., customer_id, ins_type) values (122101, ..., 2101, 'A01');
insert into contact_person (insuran_id, ..., customer_id, ins_type) values (122102, ..., 2101, 'A01');
create index order_deliver_idx1 on order_delivery(
order_dept, //고정된 선행컬럼
case when ord_type=1 then delivery_date else shipping_date end), // 중간 컬럼인덱스
item_type
)