함수 기반 인덱스
- 오라클 8.1.5 버전에서 추가
- 9iR2이하 버전에서는 Enterprise Edition에서만 지원
- 계산된 컬럼에 인덱스를 생성하여 쿼리에서 이 인덱스를 사용하는 것
- 함수 기반 인덱스는 구현하기 쉬울뿐만 아니라 효과를 즉시 알 수 있다.
- 기존 애플라케이션의 로직이나 질의를 전혀 바꾸지 않고 수행 속도를 개선할 수 있다.
- 9iR1 이하 버전에서는 아래의 권한 필요.
- QUERY REWRITE 시스템 권한(사용자의 스키마에 생성시)
- GLOBAL QUERY REWRITE 시스템 권한(다른 사용자의 스키마에 생성시)
- 옵티마이저가 함수 기반 인텍스를 사용하기 위해서 QUERY_REWRITE_ENABLED=TRUE와
QUERY REWRITEJNTEGRITY=TRUSTED을 세션 또는 시스템 레벨에서 정의- QUERY_REWRITE_ENABLED은 옵티마이저가 함수 기반 인텍스를 이용하여 쿼리 를 변형 (transformation)하도록 허용
- QUERY REWRITE_INTEGRITY는 옵티마이저가 프로그래머가 deterministic이라고 표기된 코드가 실제로 그렇다는 것을 믿으라는 의미
- DETERMINISTIC : 동일한 입력값에 대해서는 항상 통일한 결과값을 돌려준다는 것을 의미.
- 모든 버전에서 적용되는 사항
- 비용 기반 옵티마이저 (CBO)를 사용한다.
함수 기반 인텍스의 가상 컬럼 (함수가 적용된 컬럼)은 CBO에게만 인식.
규칙 기반 옵티마이저 (RBO)에 의해서는 전혀 시용되지 않는다. RBO는 함수 기반 인텍스를 구성하는 컬럼 중 아무런 함수도 적용되지 않는 선두 컬럼을 이용할 수 있다 - VARCHAR2 또는 RAW 타입을 반환하는 사용자 정의 함수로부터 리턴값을 제어하기 위해 SUBSTR을 사용한다
create table emp
as
select *
from scott.emp
where 1=0;
insert into emp
(empno,ename,job ,mgr ,hiredate ,sal ,comm,deptno)
select rownum empno,
initcap(substr(object_name,1,10)) ename,
substr(object_type, 1 ,9) JOB,
rownum MGR ，
created hiredate,
rownum SAL,
rownum COMM，
(mod(rownum ， 4)+1)*10 DEPTNO
from all_objects
where rownum < 10000;
create index emp_upper_idx on emp(upper(ename));
begin
dbms_stats.gather_table_stats(user, 'EMP' , cascade=>true);
end;
/
set autot on
select *
from emp
where upper(ename) = 'KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 1576737566
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 110 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 110 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_UPPER_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("ENAME")='KING')
- 사용자 정의 함수로 인덱스를 생성하기 위해서는 deterministic 키워드를 사용.
- 문자를 리턴하는 사용자 정의 함수에서 SUBSTR을 정의하지 않으면, VARCHAR2(4000) 유형으로 결과를 리턴.
인덱스 엔트리가 블록의 3/4이내로 정의되어야 하기 때문에 그 이상의 경우 오류 발생 (4k 블럭의 경우 오류 발생)
-- 패키지 전역변수 선언
create or replace package stats
as
cnt number default 0;
end;
/
-- 사용자 정의 함수 선언
create or replace
function my_soundex(p_string in varchar2) return varchar2
deterministic
as
l_return_string varchar2(6) default substr(p_string,1,1);
l_char varchar2(1);
l_last_digit number default 0;
type vcArray is table of varchar2(10) index by binary_integer;
l_code_table vcArray;
begin
stats.cnt :=stats.cnt+1;
l_code_table(1) := 'BPFV';
l_code_table(2) := 'CSKGJQXZ';
l_code_table(3) := 'DT';
l_code_table(4) := 'L';
l_code_table(5) := 'MN';
l_code_table(6) := 'R';
for i in 1..length(p_string)
loop
exit when (length(l_return_string) = 6);
l_char := upper(substr(p_string,i,1));
for j in 1 .. l_code_table.count
loop
if(instr(l_code_table(j),l_char) > 0 and j <> l_last_digit)
then
l_return_string :=l_return_string || to_char(j,'fm9');
l_last_digit := j;
end if;
end loop;
end loop;
return lpad(l_return_string,6,'0');
end;
/
-- 8K의 경우
create index emp_soundex_idx on
emp (my_soundex(ename), my_soundex(job));
emp (my_soundex(ename), my_soundex(job))
*
ERROR at line 2:
ORA-01450: maximum key length (6398) exceeded
-- FBI 인덱스가 없는 경우
create table emp_no_index
as
select *
from scott.emp
where 1=0;
insert into emp_no_index
(empno,ename,job ,mgr ,hiredate ,sal ,comm,deptno)
select rownum empno,
initcap(substr(object_name,1,10)) ename,
substr(object_type, 1 ,9) JOB,
rownum MGR ，
created hiredate,
rownum SAL,
rownum COMM，
(mod(rownum ， 4)+1)*10 DEPTNO
from all_objects
where rownum < 10000;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.078 0.070 0 11 0 0
Execute 1 0.218 0.225 0 3399 983 9999
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 0.296 0.295 0 3410 983 9999
-- FBI 인덱스가 있는 경우
create table emp_index
as
select *
from scott.emp
where 1=0;
create index emp_index_soundex_idx on
emp_index(substr(my_soundex(ename),1,6) );
insert into emp_index
(empno,ename,job ,mgr ,hiredate ,sal ,comm,deptno)
select rownum empno,
initcap(substr(object_name,1,10)) ename,
substr(object_type, 1 ,9) JOB,
rownum MGR ，
created hiredate,
rownum SAL,
rownum COMM，
(mod(rownum ， 4)+1)*10 DEPTNO
from all_objects
where rownum < 10000;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.062 0.063 0 16 0 0
Execute 1 0.562 0.565 0 3779 9564 9999
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 0.624 0.628 0 3795 9564 9999
-- 인덱스가 없는 경우
exec stats.cnt := 0
set autotrace on explain
variable cpu number
exec :cpu := dbms_utility.get_cpu_time
select ename, hiredate
from emp
where my_soundex(ename) = my_soundex('Kings')
;
ENAME HIREDATE
---------- ------------
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
4 rows selected.
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1900 | 25 (12)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 100 | 1900 | 25 (12)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MY_SOUNDEX"("ENAME")="MY_SOUNDEX"('Kings'))
set sutotrace off
begin
dbms_output.put_line
('cpu_time = ' || round((dbms_utility.get_cpu_time-:cpu)/100,2) );
dbms_output.put_line('function was called: ' || stats.cnt);
end;
/
cpu_time = .29
function was called: 9788
-- 인덱스가 있는 경우
create index emp_soundex_idx on
emp (substr(my_soundex(ename), 1, 6))
/
exec stats.cnt := 0
set autotrace on explain
variable cpu number
exec :cpu := dbms_utility.get_cpu_time
select ename, hiredate
from emp
where my_soundex(ename) = my_soundex('Kings')
;
ENAME HIREDATE
---------- ------------
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
4 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2259214921
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2700 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 100 | 2700 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SOUNDEX_IDX | 40 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MY_SOUNDEX"("ENAME")="MY_SOUNDEX"('Kings'))
2 - access(SUBSTR("KJWON"."MY_SOUNDEX"("ENAME"),1,6)=SUBSTR("MY_SOUNDEX"('Kings'),1,
6))
set sutotrace off
begin
dbms_output.put_line
('cpu_time = ' || round((dbms_utility.get_cpu_time-:cpu)/100,2) );
dbms_output.put_line('function was called: ' || stats.cnt);
end;
/
cpu_time = .03
function was called: 4
-- 11gR1 이상에서 지원하는 가상 컬럼 이용
drop index emp_soundex_idx;
alter table emp
add ename_soundex as (substr(my_soundex(ename), 1, 6))
/
create index emp_soundex_idx
on emp(ename_soundex);
exec stats.cnt := 0
set autotrace on explain
variable cpu number
exec :cpu := dbms_utility.get_cpu_time
select ename, hiredate
from emp
where ename_soundex = my_soundex('Kings')
;
ENAME HIREDATE
---------- ------------
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
Ku$_Chunk_ 03-NOV-11
4 rows selected.
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2259214921
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2700 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 100 | 2700 | 12 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SOUNDEX_IDX | 40 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME_SOUNDEX"="MY_SOUNDEX"('Kings'))
set sutotrace off
begin
dbms_output.put_line
('cpu_time = ' || round((dbms_utility.get_cpu_time-:cpu)/100,2) );
dbms_output.put_line('function was called: ' || stats.cnt);
end;
/
cpu_time = .05
function was called: 1
몇 개 로우에만 인덱스 하기.
- 오라클에서의 B*Tree는인덱스 키 전체가 NULL 이면 생성되지 않는다.
create index idx_t
on t( case col when 'N' then 'N' end);
선택적 유일성 구현
- 일부 상태의 데이터들에 대해서만 유일성을 보장 해야 하는 경우 NULL인 엔트리는 B*Tree에서는 만들어지지 않는 것을 이용
create unique index idx_t
on t( case when status = 'ACTIVE' then name end);
ORA-01743 주의하기
- to_date에 대해서는 어떤 경우에는 함수 기반 인덱스를 만들 수 없다.
create table t (year varchar2(4));
create index t_idx on t(to_date(year, 'YYYY'));
create index t_idx on t(to_date(year, 'YYYY'))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
create index t_idx on t(to_date('01'||year, 'MMYYYY'));
Index created.
-- YYYY 형식이 문제
-- 해당 월에 따라 데이터가 바뀔 수 있다.
select to_char(to_date('2005', 'YYYY'), 'YYYYMMDD HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('
-----------------
20051101 00:00:00
1 row selected.