함수 기반 인덱스

• 함수 기반 인덱스는 오라클 8.1 .5 버전에 추가되었다.Standard Edition에서 제공된다.반면에 오라클 9i 릴리즈 2 이전 벼전에서는 Enterprise Edition에서만 제공되었다.
• 함수 기반 인덱스는 계산된 컬럼에 인텍스를 생성하여 쿼리에서 이 인덱스를 사용하는 것을 말한다.
• 대소문자 구별이 없는 탐색이나 정렬, 또는복잡한수식에 대한 탐색등을 가능하게 하고，필요한 함수나 연산자를 구현할 수 있게 해준다.

주요한 세부 구현내용

권한에 관련된 시스템 파라미터 또는 세션 파라미터의 설정( 오라클 9i 릴리즈 1 과 그이전버젼)

• 사용자의 스키마에 있는 테이블에 함수 기반 인덱스를 생성하기 위해서는 QUERY REWRITE 시스템 권한을 가지고 있어야 한다.
• 다른 사용자의 스키마의 테이블에 함수 기반 인덱스를 생성하기 위해서는 GLOBAL QUERY REWRITE 시스템 권한을 가지고 있어야 한다.
• 옵티마이저가 함수 기반 인덱스를 사용하기 위해서 QUERY_REWRITE_ENABLED = TRUE와 QUERY REWRITE_INTEGRITY=TRUSTED을 세션 또는 시스템 레벨에서 정의한다

모든 버전에서 다음 내용이 적용된다.

• 비용 기반 옵티마이저 (CBO)를 사용한다 : 함수 기반 인덱스의 가상 컬럼 (함수가 적용된 컬럼 )은 CBO에게만 인식되고， 규칙 기반 옵티마이저 (RBO)에 의해서는 전혀 시용되지 않는다 RBO는 함수 기반 인덱스를 구성하는 컬럼 중 아무런 함수도 적용되지 않는 선두 컬럼을 이용할 수 있다 .
• VARCHAR2 또는 RAW 타입을 반환하는 사용자 정의 함수로부터 리턴값을 제어하기 위해 SUBSTR을 사용한다 : 뷰를 통해 SUBSTR을 숨길 수 있는데, 이렇게 하는것을 추천한다.

간단한 함수 기반 인덱스 예제

• EMP 테이블의 ENAME 컬럼에 대해 대소문자를 구별하지 않고 검색이 가능하게 하려고 한다.

create table test_emp
as
select *
from scott.emp
where 1=0;


insert into test_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 test_emp_upper_idx on test_emp (upper(ename)) ;

begin
dbms_stats.gather_table_stats
(user, 'TEST_EMP' , cascade=>true);
end;

select * from test_emp
where upper(ename) = 'KING'

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     2 |   110 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_EMP           |     2 |   110 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_EMP_UPPER_IDX |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("ENAME")='KING')

• 함수 기반 인덱스가 있기 전에는 EMP테이블의 모든 로우를 읽어서 대문자화하고 비교해야만 했지만 UPPER(ENAME) 값에 생성된 인덱스를 시용하게 됨에 따라 단순히 인덱스에서 상수 'KING' 을 찾아서 작은 범위구간을 읽고，최종 데이터 인덱스 키가 가리키는 rowid를 이용해서 테이블로부터 가져오게 되므로 매우빨리 처리된다.

    select my_function(ename)
      from test_emp
     where some_other_function(empno) > 10

• 로우마다 some_other_function 수행되기때문에 성능면에서 좋지 않다.

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) := 'CSKGJXZ' ;
 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 rpad( l_return_string, 6, '0' );
end;

• DETERMINISTIC 선언은 동일한 입력값에 대해서는 항상 동일한 결과값을 돌려준다는 것을 의미한다.
• 사용자 정의 함수에 대해 인덱스를만들기 위해서는 이 키워드가필요하다.
• DBMS_RANDOM.RANDOM 함수는 쓰면 동일한 값이 나오지 않으므로 쓰지말자.

set autorace on explain
variable cpu number
exec :cpu := dbms_utility.get_cpu_time

select ename, hiredate
  from test_emp
 where my_soundex(ename) = my_soundex('Kings');

ENAME      HIREDATE
---------- --------
Ku$_Chunk_ 10/03/30
Ku$_Chunk_ 10/03/30
Ku$_Chunk_ 10/03/30
Ku$_Chunk_ 10/03/30

Plan hash value: 242355602

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   100 |  1900 |    26  (16)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_EMP |   100 |  1900 |    26  (16)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MY_SOUNDEX"("ENAME")="MY_SOUNDEX"('Kings'))

set autotrace 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 = .5   --- 책에서는 0.44 초
function was called: 9955    --- 책에서는 9,948

• 쿼리를 수행 하는 데 0.44초 동안 CPU를 사용하였고 전체 태이블 스캔을 수행해야만 했다.
• MY_SOUNDEX 함수가 거의 10,000번 호출되었다 (stats.cnt 값이 9,948 이다). 그러나 함수를 더 적게호출하여 사용하는 방법이 필요하다.

함수 기반 인덱스 생성 방식에 따라 속도 향상에도 차이가 있다.

create index test_emp_soundex_idx on
test_emp( substr(my_soundex(ename) ,1,6))

• 문자를 리턴하는 사용자 정의 함수에서 SUBSTR을 정의하지 않으면 VARCHAR2 (4000) 유형으로 결과를 리턴하기 떄문에 SUBSTR 함수 사용
• 인덱스를 만들려는 함수가 숫자 또는 일자를 리턴하는 함수라면 SUBSTR 함수는 불필요하게 된다.
• 이 크기는 인덱스로 생성하기에는 너무 큰 값일 뿐 아니라 인덱스 엔트리가 블록의 3/4 이내로 정의되어야 하기때문에， 만약 그대로 생성한다면 다음과 같은 오류를 발생

-- 4k 테이블 스페이스 생성못함 => ORA-03249: 자동 세그먼트 공간 관리 테이블스페이스에 대한 동일 크기는 5 블록 이상이어야 함
create index emp_soundex_idx2 on
 emp( my_soundex(ename) ) tablespace ts4k;

ORA-01450: maximum key length (3118) exceeded    -- 책에 실린 내용

• 인덱스 엔트리가 실제로 큰값을 포함할 수 없다는것은 아니지만 데이터베이스가 권고하는사항은 결코 아니다. 그러나 SUBSTR 함수를 사용하게 되면 데이터베이스는 SUBSTR의 파라미터인 1부터 6의 정의를 통해 리턴되는 최대 문자의 크기가 6이라고 생각하게 되어 인텍스를 생성할 수 있게 한다.