在数据库优化中，索引的重要性不言而喻。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是数据量较大的时候。
虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。当然，实际上最终查询的访问路径是不会使用该虚拟索引的。
所以，虚拟索引的用处就是用来判断一个索引对于sql的执行计划的影响，尤其是对整个数据库的影响，从而判断是否需要创建物理索引。
oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个nosegment关键字即可，B*Tree index和bitmap index都可以。
不同版本的虚拟索引的特性可能不一样，本文的例子执行环境为：
PHP code:
NING@ning>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
--
1.创建虚拟索引PHP code:
NING@ning>create table test(id int,name varchar2(30));
Table created.
NING@ning>insert into test select rownum,object_name from all_objects where rownum<1001;
1000 rows created.
NING@ning>commit;
Commit complete.
NING@ning>create unique index ix_test on test(id) nosegment;
Index created.
NING@ning>analyze table test compute statistics;
Table analyzed.
--
2.使用虚拟索引PHP code:
NING@ning>explain plan for select * from test where id=1;
Explained.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
必须设置隐含参数"_use_nosegment_indexes"=true（默认为false)后，CBO才能使用虚拟索引ix_testPHP code:
NING@ning>alter session set "_use_nosegment_indexes"=true;
Session altered.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 166686173
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IX_TEST | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
RBO无法使用虚拟索引PHP code:
NING@ning>alter session set optimizer_mode=rule;
Session altered.
NING@ning>explain plan for select * from test where id=1;
Explained.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST |
----------------------------------
RBO使用hint可以使用虚拟索引PHP code:
NING@ning>explain plan for select /*+ index(test,ix_test)*/* from test where id=1;
Explained.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 166686173
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IX_TEST | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
3.虚拟索引的特性
无法执行alter indexPHP code:
NING@ning>alter index ix_test rebuild;
alter index ix_test rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index
NING@ning>alter index ix_test rename to ix_test2;
alter index ix_test rename to ix_test2
*
ERROR at line 1:
ORA-08114: can not alter a fake index
--
不能创建和虚拟索引同名的实际索引PHP code:
NING@ning>create index ix_test on test(name);
create index ix_test on test(name)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
--
可以创建和虚拟索引包含相同列但不同名的实际索引PHP code:
NING@ning>create index ix_test2 on test(id);
Index created.
--
在10g使用回收站特性的时候，虚拟索引必须显式drop，或者在drop table后purge table后，才能创建同名的索引PHP code:
NING@ning>drop table test;
Table dropped.
NING@ning>create unique index ix_test on test2(id);
create unique index ix_test on test2(id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
NING@ning>drop index ix_test;
drop index ix_test
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
NING@ning>purge table test;
Table purged.
NING@ning>create unique index ix_test on test2(id);
Index created.
--
