Virtual Index 를 생성하여 Optimizer가 인덱스를 어떻게 타는지 볼 수 있다.
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;
Virtual Index 사용하기
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 ;
SQL> select ename from myemp
2 where ename = 'SMITH';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MYEMP'
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)