ORACLE 9i FUNCTION BASED INDEX
1. 필요성
A. 조건절 컬럼에 가공을 가하는 경우 해당 컬럼에 인덱스가 존재해도 사용이 불가능하다. 이를
사용케 하기 위해 FUNCTIOIN BASED INDEX가 필요하다.
2. 예제
A. USER를 생성하고 권한을 부여
SQL> create user samp identified by samp
2 default tablespace example
3 temporary tablespace temp
4 quota unlimited on example;
User created.
(USER를 생성했다.)
SQL> grant create session,create table,select any
table to samp;
Grant succeeded.
SQL> conn samp/samp
Connected.
B. 예제로 사용할 TABLE을 생성
SQL> create table employees
2 as select employee_id, first_name,last_name
from hr.employees;
Table created.
C. AUTOTRACE를 사용하기 위한 준비
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
(PLANTABLE을 생성하는 스크립트이다.)
SQL> conn /as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> (PLUSTRACE ROLE을 생성하는 스크립트이다.)
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant plustrace to samp;
(해당
ROLE을 SAMP USER에 부여하였다.)
Grant succeeded.
SQL>
D. INDEX를 생성 후
AUTOTRACE 로 확인
(주의 : INDEX를 생성할 테이블 데이터가
작은 경우 사용블록이 작은 관계로 INDEX를 타지 않고 FTS
(FULL TABLE SCAN) 가 일어날 수 있다. )
SQL> desc employees;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EMPLOYEE_ID
NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL
VARCHAR2(25)
SQL> create index
employees_first_name_idx on employees(first_name);
Index created.
(TABLE과
INDEX 통계수집을 하였다.)
SQL> analyze table employees compute statistics;
Table analyzed.
SQL> analyze index employees_first_name_idx compute
statistics;
Index analyzed.
SQL> set autotrace traceonly explain
SQL> select * from employees
2 where first_name = 'Pat';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2
Card=1 Bytes=16)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=2 Card=
1
Bytes=16)
2 1
INDEX (RANGE SCAN) OF 'EMPLOYEES_FIRST_NAME_IDX' (NON-UN
IQUE)
(Cost=1 Card=1)
(인덱스가 있는 컬럼을 가공없이 조건절로
사용하자 정상적으로 INDEX를 타는 걸 확인 할 수 있었다.)
SQL> select * from employees
2 where upper(first_name) = 'PAT';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2
Card=1 Bytes=16)
1 0
TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=1 Bytes=16
)
(위의 경우와 달리 COLUMN에 가공을 가하자 FULL SCAN이 일어난 걸 확인할
수 있었다.)
E. FUNCTION BASED INDEX를 생성후
AUTOTRACE 로 확인
SQL> drop index employees_first_name_idx;
Index dropped.
SQL> create index employees_first_name_idx on
employees(upper(first_name));
Index created.
(함수기반 인덱스를 생성했다.)
SQL> analyze index employees_first_name_idx compute
statistics;
Index analyzed.
(새로 생성된 인덱스에 대해 통계수집을
실시하였다.)
SQL> select * from employees
2 where upper(first_name) = 'PAT';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1
Card=1 Bytes=16)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=1 Card=
1
Bytes=16)
2 1
INDEX (RANGE SCAN) OF 'EMPLOYEES_FIRST_NAME_IDX' (NON-UN
IQUE)
(Cost=1 Card=1)
(인덱스를 사용한 걸 확인할 수 있다.)
SQL>
3. 기타
A. INDEX 표현식 조회하기
i.
USER_IND_COLUMNS , USER_IND_EXPRESSIONS
사용
SQL> select index_name,table_name,column_name
2 from user_ind_columns;
INDEX_NAME
TABLE_NAME COLUMN_NAME
------------------------------ ---------- ------------------------------
EMPLOYEES_FIRST_NAME_IDX
EMPLOYEES SYS_NC00004$
SQL> select * from user_ind_expressions;
INDEX_NAME
TABLE_NAME COLUMN_EXPRESSION
COLUMN_POSITION
------------------------------ ---------- --------------------
---------------
EMPLOYEES_FIRST_NAME_IDX EMPLOYEES UPPER("FIRST_NAME") 1
ii.
dbms_metadata.get_ddl 이용하기
SQL> select index_name,table_name,
2 dbms_metadata.get_ddl('INDEX',index_name)
3 from user_indexes;
INDEX_NAME
TABLE_NAME METADATA
------------------------------ ----------
--------------------------------------------------
EMPLOYEES_FIRST_NAME_IDX
EMPLOYEES
CREATE INDEX "SAMP"."EMPLOYEES_FIRST_NAME_IDX" O
N
"SAMP"."EMPLOYEES" (UPPER("FIRST_NAME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATI
STICS
STORAGE(INITIAL 20480 NEXT 20480 MINEXTENTS 1 MA
XEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFF
ER_POOL DEFAULT)
TABLESPACE
"EXAMPLE"
