一、引言
DBA在日常维护管理数据库进行低性能SQL分析时，有时候需要通过创建索引对SQL进行优化，但有些时候我们创建的索引是否能用到？这个只能创建以后才能看出效果，但是在实际工作中，特别是对大表创建索引对系统性能有很大影响，因此我们不得不避开业务高峰时段，但是有没有一种办法创建索引而不影响性能呢？有，那就是虚拟索引。
虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。作用仅仅是为了DBA作SQL优化时使用，DBA根据虚拟索引的优化效果决定是否创建物理索引。
二、虚拟索引类型
虚拟索引支持B-TREE索引和BIT位图索引，在CBO模式下ORACLE优化器会考虑虚拟索引，但是在RBO模式下需要添加hint才行。
三、虚拟索引创建实例
SQL> drop table t purge;表已删除。SQL> create table t as select * from dba_objects;表已创建。--创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为trueSQL> alter session set "_use_nosegment_indexes"=true;会话已更改。--虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字SQL> create index ix_t_id on t(object_id) nosegment;索引已创建。SQL> explain plan for select * from t where object_id=1;已解释。SQL> set linesize 1000SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 206018885---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 5 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_T_ID | 273 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 2 - access("OBJECT_ID"=1)Note----- - dynamic sampling used for this statement (level=2)已选择18行。SQL> set autotrace traceonlySQL> select * from t where object_id=1;未选定行执行计划----------------------------------------------------------Plan hash value: 206018885---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 12 | 2484 | 5 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IX_T_ID | 273 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=1)Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 4 recursive calls 0 db block gets 1101 consistent gets 753 physical reads 0 redo size 1184 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processedSQL> set autotrace off--以下看的是真实执行计划，显然是用不到索引。SQL> alter session set statistics_level=all;会话已更改。SQL> select * from t where object_id=1;未选定行SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 2qhwh0nzrzx2r, child number 1-------------------------------------select * from t where object_id=1Plan hash value: 1601196873------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1037 ||* 1 | TABLE ACCESS FULL| T | 1 | 12 | 0 |00:00:00.01 | 1037 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID"=1)Note----- - dynamic sampling used for this statement (level=2)已选择22行。--从数据字段中是无法找到这个索引的。SQL> select index_name,status from user_indexes where table_name='T';未选定行 四、虚拟索引的特点
4.1、虚拟索引无法执行alter index选项SQL> alter index IX_T_ID rebuild;alter index IX_T_ID rebuild*第 1 行出现错误:ORA-08114: 无法变更假索引
4.2、使用回收站特性的时候，虚拟索引必须显示drop，才能创建同名的索引。
SQL> create index ind_status on t(status);索引已创建。SQL> drop table t;表已删除。SQL> flashback table t to before drop;闪回完成。SQL> select table_name,index_name,status from user_indexes where table_name='T';TABLE_NAME INDEX_NAME STATUS------------------------------ ------------------------------ --------T BIN$7jAFlUG6b1zgQAB/AQAPyw==$0 VALIDSQL> create index ind_object_id on t(object_id);索引已创建。SQL> create index inds_status on t(status);create index inds_status on t(status) *第 1 行出现错误:ORA-01408: 此列列表已索引
4.3、不能创建和虚拟索引同名的实际索引；
4.4、可以创建和虚拟索引包含相同列但不同名的实际索引；4.5、虚拟索引分析并且有效，但是数据字典里查不到结果。