2.3 함수기반 인덱스

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

정의

테이블의 컬럼들을 가공한 값으로 인덱스로 생성한 것이다.

{section}
{column:width=33%}


함수기반 인덱스를 생성하면 오라클서버가 생성한  인덱스컬럼을 확인할 수 있다.

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$

{column}
{section}

함수기반 인덱스를 사용하기 위한 준비

SQL>alter session set QUERY_REWRITE_ENABLE = TRUE;
SQL>alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
SQL>grant query rewrite to scott;

사용가능한 함수(식)

사용가능한 형식은 테이블의 열, 상수, sql함수 및 사용자가 정의한 함수이다.

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

기본적인 제약사항

비용기준 옵티마이져(CBO)에서만 사용가능한다
함수기반 인덱스를 생성한 후 반드시 통계정보를 생성해야 한다.
사용자지정함수는 반드시 DETERMINISTIC으로 선언되어야 한다.
QUERY_REWRITE_ENABLE parameter 는 TRUE로 선언되어야 한다.
QUERY_REWRITE_INTEGRITY parameter는 TRUSTED로 선언되어야 한다.
다음의 사용자 권한을 가져야한다.
- 인덱스 생성권한 : INDEX CREATE / ANY INDEX CREATE
- 쿼리 재생성권한 : QUERY REWRITE / GLOBAL QUERY REWRITE
함수나 수식의 결과가 NULL인 경우는 이 인덱스를 통해 액세스할 수 없다.
사용자 지정함수를 사용한 경우에는 종속성 유지에 주의해야 한다.
- 인덱스 정의에 사용된 사용자 함수가 재정의되었을 때 사용불가 상태가 된다.
- 인덱스 소유자의 실행 권한이 취소 되면 사용불가 상태가 된다.
옵티마이져가 사용불가 상태가 된 인덱스를 선택하면 SQL의 실행은 실패한다.
- 인덱스를 가용상태로 만들거나 재구축해야 한다.
- 인덱스를 미사용상태로 만든다 이때 SKIP_UNUSABLE_INDEXES파라미터가 TRUE로 선언되어 있어야 한다.
스칼라 서브쿼리로 표현된 컬럼은 함수기반 인덱스를 사용할 수 없다.
값이 상황에 따라 달라질 수 있는 SYSDATE, USER, ROWNUM 등의 가상컬럼이 포함되면 이 인덱스를 생성할 수 없다.
파티션을 적용한 경우에 파티션 키를 함수기반 인덱스에 사용할 수 없다.
숫자 컬럼을 문자 연산하거나 문자컬럼을 수치연산하는 수식의 경우에는 직접 기술하지 않았더라도 내부적으로 TO_CHAR, TO_NUMBER가 추가되어 처리된다.
함수기반 인덱스에서는 NLS파라메터를 현재 기준으로 적용하기 때문에 만약 세션 레벨에서 재정의를 한다면 잘못된 결과를 얻을 수도 있으므로 주의해야 한다.
WHERE 절에 기술된 컬럼이 표현된 것과 인덱스에 지정된 표현이 다르더라도 논리적으로 교환법칙이 성립하는 경우라면 같은 결과를 얻을 수 있다.

2.3.3 함수기반 인덱스의 활용

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

컬럼의 중간부분을 검색
컬럼의 분할 원칙을 준수하지 않았기 때문에 발생하는 문제에 대한 해결방안
{section}
{column:width=33%}


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);

{column}
{section}

조인 연결고리 컬럼이 대응하지 않는 경우의 해결
정상적인 데이터 모델링을 수행했다면 나타날 수 없겠지만 현실에서는 가끔 등장하는 형태.
상위테이블의 품목분류테이블(itemgrop)에서는 상세한 관리를 위해 컬럼을 대분류(class1), 중분류(class2), 소분류(class3)으로 나누었으나 하위 테이블(items)에서는 컬럼이 늘어날 것을 염려하여 그룹코드(group_cd)라는 하나의 컬럼으로 생성한 예이다
다음과 같이 조인을 할 경우 한쪽 연결고리에 이상이 발생하여 조인에 나쁜 영향을 미칠 수 있다.
{section}
{column:width=33%}


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)

{column}
{section}
위와 같은 경우 다음과 같은 함수기반 인덱스로 해결할 수 있다.
{section}
{column:width=33%}


create index group_cd_idx on item_group(x.class1 || x.class2 || x.class3 );

{column}
{section}

일자컬럼이 분할 된 경우의 해결
일자컬럼이 함부로 년,월,일로 분할 되어 관리되는 경우 년,월,일을 결하여 비교할 수 밖에 없으므로 조인 연결고리 컬럼이 대응하지 않는 경우과 같아진다.
{section}
{column:width=33%}


create index sal_date_idx on sales (sal_yyyy || sal_mm || sal_dd);

{column}
{section}

데이터 타입이 상이한 조인 컬럼
표준화를 준수하고 도메인을 사전에 체계적으로 관리하였다면 발생하지 않을 확률이 높겠지만 어떻한 이유든 조인되어야 하는 테이블간의 조인키 컬럼의 데이터타입이 일치하지 않는 경우 컬럼의 내부적 변형이 발생하여 어느 한쪽의 인덱스가 사용될 수 없는 상태가 될 수 있다.
이런 경우 함수기반인덱스에서 한쪽 컬럼의 데이터형을 미리 가공하여 인덱스를 생성하면 해결할 수 있다.
{section}
{column:width=33%}


create index deptno_idx on emp (to_number(deptno));

{column}
{section}

조인컬럼이 경우에 따라 달라지는 경우의 조인
아래와 같이 업무규칙에 따라 배타적으로 조인하는 경우가 발생했다면 조인시 컬럼이 달라질 수 있다.
이때 드라이빙을 원하는대로 하기 위해 식에 함수기반 인덱스를 사용하면 가능하게 유도할 수 있다.
{section}
{column:width=33%}


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);

{column}
{section}

부모테이블의 컬럼과 결합한 인덱스 생성
인덱스의 사용 시 두개의 인덱스를 동시에 쓰기어려운 제한 조건이 있었는데 하나의 선행인덱스가 사용된다면 다른 인덱스는 체크조건으로 사용된다는 점이다. (인덱스머지는 제외)
{section}
{column:width=33%}


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);

{column}
{section}

단 함수내에 참조 테이블이 있을 경우 버전 별로 차이가 있으므로 주의해서 사용해야한다.

?

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

대소문자나 공백이 혼재된 컬럼의 검색
대소문자가 혼재되었을 경우
{section}
{column:width=33%}


create index ename_upper_idx on employees (upper (ename));

{column}
{section}

불필요한 공백을 제거한 후 비교를 해야하는 경우
{section}
{column:width=33%}


create index ename_trim_idx on employees(replace(ename,' '));

{column}
{section}

null값을 치환하여 검색
null값일 때는 디폴트값을 넣어 비교할 경우
{section}
{column:width=33%}


create index end_date_idx on account_history (nvl(end_date, '99991231'));

{column}
{section}

접두사를 채워서 검색
전화번호 앞번호가 018이면 그냥 두고 아니면 016을 붙여서 비교할 때
{section}
{column:width=33%}


create index call_number_idx on call_data (decode(substr(call_number,1,3),'018','','016')||call_number);

{column}
{section}

가공처리 결과의 검색

복잡한 계산결과의 검색
상품별로 데이터량이 많을 때 주문단가에서 할인금액을 뺀 실제 적용단가에 주문수량을 곱하여 금액별로 100개의 상품을 조회할때 복잡한 계산식을 함수기반인덱스로 만들어 결과값을 논리컬럼으로 저장해두면 힌트에서 인덱스를 사용할 수 있다.
{section}
{column:width=33%}


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

{column}
{section}

말일, 단가, 율의 검색
해당월에 가장 높은 sal_amount가 발생한 건을 찾는 경우
{section}
{column:width=33%}


create index sal_amount_idx on sales (last_day(sal_date),   sal_amount);

{column}
{section}

기간, 컬럼, 길이 검색
- 기간을 처리하는 경우
  {section}
  {column:width=33%}


create index term_idx on activities (expire_date - start_date)

{column}
{section}
-- 길이를 검색하는 경우
{section}
{column:width=33%}


create index source_length_idx on print_media(text_length(source_text));

{column}
{section}

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

육면체(cube)를 오브젝트타입으로 생성하오 이것을 이용하여 부피를 구하는 예
{section}
{column:width=33%}


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));

{column}
{section}

배타적 관계의 인덱스 검색

배타적관계의 유일성을 보장
어떤 배타적 관계에 있는 속성들을 관찰하면 동일한 속성(subtype)으로 볼 수 있으며 이들을 결합했을 경우 반드시 존재해야한다거나 유일해야 한다는 원칙이 있을 수 있다. 이런경우 함수기반 인덱스를 생성하여 처리의 단순화 및 시스템을 통한 제약조건의 검증까지 보장받을 수 있다.

검색 시 customers 테이블의 cust_type에 따라 사업자번호 또는 주민번호의 내용을 조회한다.
{section}
{column:width=33%}


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');

{column}
{section}

배타적관계의 결합인덱스
어떤 구분값을 기준으로 결합인덱스의 구성이 달라진다면 함수기반인덱스로 해결할 수 있다.
{section}
{column:width=33%}


create index order_deliver_idx1 on order_delivery(
order_dept, //고정된 선행컬럼
case when ord_type=1 then delivery_date else shipping_date end), // 중간 컬럼인덱스
item_type 
)

{column}
{section}

문서에 대하여

이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^