一、背景

在生产环境上，经常会出现一些生产环境有而测试环境没有的性能问题，而这些性能问题可能是由于某条语句性能差导致的，索引通常是优化SQL的很好手段，但是在生产环境任何的DDL操作都应该谨慎，创建没加online的索引时，会对该对象的DML操作产生阻塞。这是我们假象，如果这张表有这个索引，那又会生成什么样的执行计划，这就是虚拟索引的用处。

适用：OACLE 11G及以后的版本

二、虚拟索引概念

虚拟索引指没有创建对应的物理实体的索引，虚拟索引的目的，是在不耗时、CPU、IO、大量存储空间去实际创建索引的情况下，来判断一个索引是否能够对SQL优化起到作用。nosegment选项表明索引是虚拟的，_use_nosegment_indexes指示数据库可以在执行计划中使用虚拟索引。

三、示例 

SQL> create table temp_123 as select * from user_objects;

表已创建。

SQL> explain plan for select * from temp_123 where object_id=1630654;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               

--------------------------------------------------------------------------------

Plan hash value: 3542478759                                                     

------------------------------------------------------------------------------  

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  

------------------------------------------------------------------------------  

|   0 | SELECT STATEMENT  |          |    10 |  1600 |   315   (1)| 00:00:05 |  

|*  1 |  TABLE ACCESS FULL| TEMP_123 |    10 |  1600 |   315   (1)| 00:00:05 |  

------------------------------------------------------------------------------  

Predicate Information (identified by operation id):                             

---------------------------------------------------                             

PLAN_TABLE_OUTPUT                                                               

--------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=1630654)                                              

Note                                                                            

-----                                                                           

   - 'PLAN_TABLE' is old version                                                

   - dynamic sampling used for this statement                                   

已选择18行。

SQL> alter session set "_use_nosegment_indexes"=true;

会话已更改。

SQL> create index idx_temp on temp_123(object_id) nosegment;

索引已创建。

SQL> explain plan for select * from temp_123 where object_id=1630654;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                               

--------------------------------------------------------------------------------

Plan hash value: 4276853690                                                     

--------------------------------------------------------------------------------

--------                                                                        

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

me     |                                                                        

--------------------------------------------------------------------------------

--------                                                                        

PLAN_TABLE_OUTPUT                                                               

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |    10 |  1600 |     5   (0)| 00

:00:01 |                                                                        

|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP_123 |    10 |  1600 |     5   (0)| 00

:00:01 |                                                                        

|*  2 |   INDEX RANGE SCAN          | IDX_TEMP |   626 |       |     1   (0)| 00

:00:01 |                                                                        

--------------------------------------------------------------------------------

--------                                                                        

PLAN_TABLE_OUTPUT                                                               

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):                             

---------------------------------------------------                             

   2 - access("OBJECT_ID"=1630654)                                              

Note                                                                            

-----                                                                           

   - 'PLAN_TABLE' is old version                                                

   - dynamic sampling used for this statement                                   

已选择19行。

SQL> SELECT * FROM USER_INDEXES WHERE INDEX_NAME='IDX_TEMP';

未选定行