Oracle虚拟索引的运用，oracle虚拟索引
在做SQL调优的时候，有的时候需要加一个索引，测试下对性能提升有没有帮组，如果此时这张表非常大，建索引将会非常之麻烦，这种场景虚拟索引就该登场了。下面来做个试验：
SQL> select * from v$version;
BANNER----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> drop table test purge;
SQL> create table test as select * from user_objects;
SQL> select count(1),
count(distinct object_name) c_oname,
count(distinct object_type) s_otype
from test;
COUNT(1) C_ONAME S_OTYPE
---------- ---------- ----------
11500 11430 14
SQL> create index ind_test_name on test(object_name) nosegment;
SQL> create index ind_test_otype on test(object_type) nosegment;
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
SQL> --必须设置隐含参数”_use_nosegment_indexes”=true（默认为false)后，CBO才能使用虚拟索引SQL> alter session set "_use_nosegment_indexes"=true;
SQL> set autotrace trace exp
SQL> select * from test where object_name='TEST';执行计划
----------------------------------------------------------
Plan hash value: 3675505035
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 86 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_NAME | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')
SQL> select * from test where object_type='TEST';
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 821 | 70606 | 36 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 821 | 70606 | 36 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TEST')
SQL> set autotrace off
SQL> select index_name from user_indexes s where s.table_name ='TEST';
未选定行
SQL> col object_name format a15;
SQL> select object_name,object_id,object_type from user_objects s
where s.object_name in ('IND_TEST_NAME', 'IND_TEST_OTYPE');
OBJECT_NAME OBJECT_ID OBJECT_TYPE
--------------- ---------- -------------------
IND_TEST_NAME 670666 INDEX
IND_TEST_OTYPE 670667 INDEX
SQL> select segment_name,s.bytes from user_segments s where s.segment_name
in ('IND_TEST_NAME', 'IND_TEST_OTYPE');
未选定行
原理：加了虚拟索引后，同时开启_use_nosegment_indexes，CBO在评估执行计划的时候会把虚拟索引当做是一个真实的索引，有点欺骗CBO的意思。不能在user_indexes和user_segments中找到，但在user_objects可以找到。说明虚拟说只是加了数据字典，并没有产生实际的数据，那它也应该不能像真实的索引哪有操作，再做一个试验：
SQL> alter index ind_test_name rebuild;
alter index ind_test_name rebuild
*
第 1 行出现错误:
ORA-08114: 无法改变假索引
最后：虚拟索引只适合做性能调优，加了之后记得要删除。
创建索引不是为了在sql语句中用的，而是可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
如果你的sql语句适合用这条索引的话，oracle的CBO在执行sql语句的时候就会使用index的方式，你没必要自己限制它使用，认为的强制改变oracle的执行计划对oracle的效率很危险，当然你要是一定要在你的sql使用，那么就在sql添加Hint,具体sql语句为：
select /*+ index（emp non_deptno）*/ * from emp
强烈建议提问者不要这样写，原因上面已经说了。