虚拟索引(virtual index)是指没有创建对应的物理实体的索引。虚拟索引不分配具体的索引段，只是告诉优化器假设有个索引，用来测试是否需要添加索引

需要首先设置

alter session set optimizer_mode=rule;

alter session set "_use_nosegment_indexes"=true; 

查看虚拟索引

SELECT index_owner, index_name 
FROM dba_ind_columns  
WHERE index_name NOT LIKE 'BIN$%' 
MINUS  
SELECT owner, index_name FROM dba_indexes;

删除虚拟索引

drop index user01.INDEX_NAME_TEST03;

虚拟索引实验

USER01@aaron> create index vindex_test01 on test01(object_id) nosegment;

Index created.

USER01@aaron> alter session set "_use_nosegment_indexes"=TRUE;

Session altered.

USER01@aaron> explain plan for select * from test01 where object_id=54;

Explained.

USER01@aaron> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2865676745

---------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |     32 |  3104 |       2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST01         |     32 |  3104 |       2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN         | VINDEX_TEST01 |     32 |         |       1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OBJECT_ID"=54)

14 rows selected.