Oracle 9i Tuning Pack의 new features인 Virtual Index에 대해 알아보겠습니다.
Oracle 9i의 OEM Tuning Pack에서 "virtual index wizard" 를 이용하여 CBO 옵티마이저 환경에서 생성할 인덱스가 어떻게 이용되는지 평가할수 있습니다.
인덱스 생성시 nosegment 라는 절을 이용합니다.
Create Index 명령과 함께 nosegment 옵션을 사용하여 가상 인덱스를 만들 수 있는데 이 인덱스는 실제 하드 디스크에 Segment로 존재하는 것이 아니라 Data Dictionary안에 정의 됩니다.
If you own the extra cost Oracle Tuning Pack, you will note the "virtual index wizard" area.
According to Oracle, virtual indexes will help you determine how the Oracle cost-based SQL optimizer (CBO) will evaluate and use the potential index.
Because the virtual index is a fake index, Oracle will never be able to use it, but you can use the hidden _use_nosegment_indexes parameter to evaluate execution plans for virtual indexes:
set autotrace on explain;
alter session set "_use_nosegment_indexes" = true;
아래의 예제를 보도록 합니다.
==================================================
SQL> desc myEmp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(14)
ENAME VARCHAR2(30)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(10,2)
COMM NUMBER(10,2)
DEPTNO NUMBER(4)
SQL> select ename from myemp
2 where ename = 'SMITH';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MYEMP'
SQL> create index IDX_MYEMP_VIR_ENAME on myemp(ename) nosegment;
;
Virtual index를 사용하기 위해서는 Hidden parameter 인 _use_nosegment_indexes를 설정해야 합니다.
SQL>alter session set "_use_nosegment_indexes" = true;
SQL> select ename from myemp
2 where ename = 'SMITH';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'IDX_MYEMP_VIR_ENAME' (NON-UNIQUE)