ORACLE虛擬索引(Virtual Index) 虛擬索引概念 虛擬索引（Virtual Indexes）是一個定義在數據字典中的假索引（fake index），它沒有相關的索引段。虛擬索引的目的是模擬索引的存在而不用真實的創建一個完整索引。這允許開發者創建虛擬索引來查看相關執行計劃而不用等到真實... ...
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.0SQL> create table test
2 as3 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>
但是實際執行計劃還是走全表掃描，如下測試。
