虛拟索引（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