索引类型之虚拟索引

使用场景： 

   如果想创建一个非常大的索引，并且不分配空间，以便确定优化器能否会用到它;当处理大表时，并要迅速弄清楚优化器是否将使用该表上的一个潜在索引时，我们可以使用虚拟索引，这样开发人员就可以如同该索引实际存在那么运行解释计划，而不必等待一个大的索引被创建完成。如果确定该索引有用，再删除该索引并使用不带nosegment子句的命令重新创建它。

 

先不分配空间:

SQL> create index fake_idx on employees(last_name) nosegment;

Index created.
 

SQL>  select index_name,column_name,table_name

          from dba_ind_columns
        where index_name like 'FAKE%';

INDEX_NAME              COLUMN_NAME          TABLE_NAME
------------                 ------------------         ----------------- 
FAKE_IDX                   LAST_NAME               EMPLOYEES
 

SQL> select index_name,table_name from dba_indexes
  2* where index_name like 'FAKE%'
SQL> /

no rows selected

此查询不返回任何行的原因是，dba_indexes视图只显示有实际索引段的信息，因为虚拟索引并没有真正使用存储空间，所以不会显示在这个视图中。

 

可以通过隐式参数开启:

alter session set "_use_nosegment_indexes"=true;

使用这个参数来检查查询的执行计划，看它是否将使用该索引。

SQL> create  index virtual_idx
  2  on emp(ename) nosegment;

Index created.

SQL> set autotrace on explain
SQL> alter session set "_use_nosegment_indexes"=true
SQL> /

Session altered.
SQL> select ename from emp where ename='KING';
ENAME
----------
KING

Execution Plan
----------------------------------------------------------
Plan hash value: 1165707112
-------------------------------------------------------------------------------- ------
| Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time | 

-------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT |                     |     1 |     5 |     2   (0)| 00:00:01 | 

|*  1 |  INDEX RANGE SCAN| VIRTUAL_IDX |     1 |     5 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ENAME"='KING') 

使用索引，看是否有用，如果有用，再drop index, 去掉nosegment,重建。

SQL> drop index virtual_idx;

Index dropped. 

即使在某列上创建了虚拟索引，在同一列上还是可以创建普通索引。

优化器对虚拟索引使用基本默认优惠券统计信息，这与没有收集任何统计信息的正常索引是一样的。