昨天简单总结了下不可见索引，今天来说说虚拟索引。
这两个索引听起来有点类似。其实差别还是比较大。
不可见索引有对应的索引段，而虚拟索引没有对应的索引段存在。
不可见索引可以通过alter语句来直接切换可见不可见。而对于虚拟索引而言这些操作都不支持。
不可见索引可以在user_indexes中查到对应的数据字典信息。但是虚拟索引在user_indexes中都没有记录，最后只能从dba_objects里面勉强查到一条它存在的记录。
不可见索引和虚拟索引都有对应的数据库参数，可以通过alter session,system来修改生效。
不可见索引在优化器中进行了屏蔽，使得索引的可见/不可见都可以灵活的切换，而虚拟索引是在希望在优化器中做标识，使得语句的执行计划能够考虑到对应的虚拟索引的作用。
个人觉得，在日常的使用中对于索引创建存在争议的场景中，可以考虑使用虚拟索引，来通过查看执行计划来比较前后的变化。如果提升的幅度很大，再考虑创建对应的索引。

我们来举个简单的例子来说明一下虚拟索引。
我们来创建一个表，然后首先验证创建一个普通索引验证索引能够正常启用，然后删除索引，创建虚拟索引来看看语句的执行情况。
创建表t
SQL> create table t as select *from dba_objects where object_id is not null and rownum<100000;
Table created.
然后随机抽取4条数据。
SQL> select *from (select object_id from t order by dbms_random.value()) where rownum<5;
OBJECT_ID
----------
22969
20703
13851
8040
SQL> set autot trace exp stat
我们来看看当前的执行计划，因为没有索引，索引会走全表扫描。
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1233 | 249K| 267 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1233 | 249K| 267 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
1525 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们创建对应的索引。来看看语句的执行情况。
SQL> create unique index inx_t on t(object_id) ;
Index created.
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
1 physical reads
0 redo size
1499 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
删除索引，创建一个虚拟索引。来对比一下执行计划的情况。
SQL> drop index inx_t;
Index dropped.
SQL> create unique index inx_t on t(object_id) nosegment;
Index created.
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1233 | 249K| 267 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1233 | 249K| 267 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
1487 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
发现创建了虚拟索引之后，这个测试的效果跟没有创建一样。我们来看看使用hint是否有作用。
SQL> select /*+index(t inx_t)*/ *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1233 | 249K| 267 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1233 | 249K| 267 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1483 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这个时候，我们得知虚拟索引没有启用，至于启用的方法，就是通过一个数据库参数_use_nosegment_indexes来实现。可以在session,system级别做设置。一般来说我们在session级做简单的对比测试，如果执行计划的效果提升很多，然后可以根据情况再创建存在段的索引。或者在系统级开启这个开关。
我们先在session级别开启。
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;
Session altered.
我们来查看一下语句的执行情况。
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INX_T | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1483 consistent gets
0 physical reads
0 redo size
1632 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，索
引被启用了。其实这个过程中的资源消耗很低，因为没有对应的索引段存在，完全是根据优化器的判断。
我们关闭这个开关。
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = false;
Session altered.
尝试使用alter语句来rebuild这个虚拟索引。
SQL> alter index inx_t rebuild;
alter index inx_t rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index
alter语句在虚拟索引中式不支持的。
我们来查查数据字典里的信息。
SQL> set autot off
SQL> select segment_name,segment_type,blocks from user_segments where segment_name='INX_T';
no rows selected
SQL> select index_name ,dropped ,segment_created from user_indexes where index_name='INX_T';
no rows selected
在user_segments,user_indexes中都没有对应的记录存在，我都怀疑索引是否存在。
SQL> create unique index inx_t on t(object_id) nosegment;
create unique index inx_t on t(object_id) nosegment
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
最后在dba_objects里面终于找到一条记录。
SQL> select object_name, object_type from dba_objects where object_name = 'INX_T';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
INX_T INDEX

关于虚拟索引，可以在metalink上参考Virtual Indexes (Doc ID 1401046.1)