ORACLE虚拟索引(Virtual Index)
虚拟索引概念
虚拟索引（Virtual Indexes）是一个定义在数据字典中的假索引（fake index），它没有相关的索引段。虚拟索引的目的是模拟索引的存在而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。如果我们观察到优化器生成了一个昂贵的执行计划并且SQL调整指导建议我们对某些的某列创建索引，但在生产数据库环境中创建索引与测试并不总是可以操作。我们需要确保创建的索引将不会对数据库中的其它查询产生负面影响，因此可以使用虚拟索引。
A virtual index is a "fake" index whose definition exists in the data dictionary, but has no associated index segment. The purpose of virtual indexes is to simulate the existence of an index - without actually building a full index. This allows developers to run an explain plan as if the index is present without waiting for the index creation to complete and without using additional disk space. If we observe that optimizer is creating a plan which is expensive and SQL tuning advisor suggest us to create an index on a column, in case of production database it may not be always feasible to create an index and test the changes. We need to make sure that the created index will not have any negative impact on the execution plan of other queries running in the database.So here is why a virtual index comes into picture.
虚拟索引应用
虚拟索引是Oracle 9.2.0.1以后开始引入的，虚拟索引的应用场景主要是在SQL优化调优当中，尤其是在生产环境的优化、调整。这个确实是一个开创性的功能，试想，如果一个SQL性能很差，但是涉及几个数据量非常大的表，你尝试新增一个索引，但是你也不确定优化器一定就能使用该索引或者使用该索引后，执行计划就能朝着预想的那样发展，但是在大表上创建索引、删除索引也是一个代价非常高的动作，有可能引起一些性能问题或者影响其他SQL的执行计划，而且创建一个实际的索引需要较长的时间，而虚拟索引几乎非常快速，在性能优化和调整中经常被使用。其实说白了，虚拟索引主要是给DBA做SQL优化使用，根据它的测试效果来判断是否需要创建实际索引。
虚拟索引测试
创建一个测试表，我们在这个测试表上做一些实验。
SQL> set linesize 1200
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0
SQL> create table test
2 as
3 select * from dba_objects;
Table created.
SQL> set autotrace traceonly explain;
SQL> select * from test where object_id=60;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 11 | 2277 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=60)
Note
-----
- dynamic sampling used for this statement (level=2)
创建虚拟索引，检查执行计划是否走索引扫描。实际上创建虚拟索引就是普通索引语法后面加一个NOSEGMENT关键字即可，B*TREE INDEX和BITMAP INDEX都可以。
SQL> set autotrace off;
SQL>
SQL> create index idx_test_virtual on test(object_id) nosegment;
Index created.
SQL> set autotrace traceonly explain;
SQL> select * from test where object_id=60;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 11 | 2277 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=60)
Note
-----
- dynamic sampling used for this statement (level=2)
如上所示，并没有使用虚拟索引。如果要使用所创建的虚拟索引，必须设置隐含参数"_USE_NOSEGMENT_INDEXES"=TRUE（默认为FALSE)后CBO优化器模式才能使用虚拟索引，RBO优化器模式无法使用虚拟索引
SQL> alter session set "_USE_NOSEGMENT_INDEXES"=true;
Session altered.
SQL> select * from test where object_id=60;
Execution Plan
----------------------------------------------------------
Plan hash value: 1235845473
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 11 | 2277 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_VIRTUAL | 263 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=60)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
但是实际执行计划还是走全表扫描，如下测试。
SQL> set autotrace off;
SQL> select * from test where object_id=60;
...............
SQL> select sql_id, child_number,sql_text
2 from v$sql
3 where sql_text like '%select * from test%60%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ---------------------------------------
6t76zuzdgc4d9 0 select * from test where object_id=60
76rkkrw0j254p 0 select sql_id, child_number,sql_text from v$sql where sql_text like '%select * from test%60%'
SQL> select * from table(dbms_xplan.display_cursor('6t76zuzdgc4d9'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID 6t76zuzdgc4d9, child number 0
-------------------------------------
select * from test where object_id=60
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 282 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 11 | 2277 | 282 (1)| 00:00:04 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=60)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
查看数据库有没有创建对应的虚拟索引，可以使用下面SQL语句查询。
SELECT INDEX_OWNER, INDEX_NAME
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME NOT LIKE 'BIN$%'
MINUS
SELECT OWNER, INDEX_NAME
FROM DBA_INDEXES;
--或下面SQL（下面SQL在有些情况下有bug）
SELECT O.OBJECT_NAME AS FAKE_INDEX_NAME
FROM DBA_OBJECTS O
WHERE O.OBJECT_TYPE = 'INDEX'
AND NOT EXISTS (SELECT NULL
FROM DBA_INDEXES I
WHERE O.OBJECT_NAME = I.INDEX_NAME
AND O.OWNER = I.OWNER);
虚拟索引特点
虚拟索引跟普通索引是有所区别的。主要体现在下面一些地方。
1： 创建虚拟索引后需要设置隐含参数"_use_nosegment_indexes"为true， oracle才会选择虚拟索引。上面实验已经验证。
2： 虚拟索引只存在数据字典中定义，没有相关的索引段。如下所示，在dba_objects能查到索引定义，但是dba_indexes中没有数据。
SQL> select index_name from dba_indexes where table_name='TEST';
no rows selected
SQL> col object_name for a32;
SQL> col object_type for a32;
SQL> select object_name, object_type from dba_objects where object_name=upper('idx_test_virtual');
OBJECT_NAME OBJECT_TYPE
-------------------------------- --------------------------------
IDX_TEST_VIRTUAL INDEX
3: 虚拟索引也可以像普通索引那样分析analyze;但是没有相关统计信息生成（内部机制不清楚）
SQL> analyze index idx_test_virtual validate structure;
Index analyzed.
SQL>
4: 虚拟索引不能重建rebuild，否则会抛出ORA-8114错误。
SQL> alter index idx_test_virtual rebuild;
alter index idx_test_virtual rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index
5：不能创建与虚拟索引同名的普通索引
SQL> create index idx_test_virtual on test(object_id);
create index idx_test_virtual on test(object_id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
6：删除虚拟索引是不会放入到回收站的
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> drop index idx_test_virtual;
Index dropped.
SQL> select owner, object_name, original_name, type from dba_recyclebin
2 where original_name='IDX_TEST_VIRTUAL';
no rows selected
参考资料：
Fake Indexes in Oracle RDBMS (文档 ID 329457.1)
Virtual Indexes (文档 ID 1401046.1)