[[201247]]

虛擬索引是定義在數(shù)據(jù)字典中的偽索引，但沒有相關(guān)的索引段。虛擬索引的目的是模擬索引的存--而不用真實(shí)的創(chuàng)建一個(gè)完整索引。這允許開發(fā)者創(chuàng)建虛擬索引來查看相關(guān)執(zhí)行計(jì)劃而不用等到真實(shí)創(chuàng)建完索引才能查看索引對執(zhí)行計(jì)劃的影響，并且不會增加存儲空間的使用。如果我們觀察到優(yōu)化器生成了一個(gè)昂貴的執(zhí)行計(jì)劃并且SQL調(diào)整指導(dǎo)建議我們對某些的某列創(chuàng)建索引，但在生產(chǎn)數(shù)據(jù)庫環(huán)境中創(chuàng)建索引與測試并不總是可以操作。我們需要確保創(chuàng)建的索引將不會對數(shù)據(jù)庫中的其它查詢產(chǎn)生負(fù)面影響，因此可以使用虛擬索引。

下面舉例進(jìn)行說明:

1.創(chuàng)建一個(gè)測試表test

SQL> create table test as select * from dba_objects; 
Table created.

2.從表test查詢object_name等于standard的記錄

SQL> select * from test where object_name='STANDARD'; 
 
OWNER 
 
------------------------------ 
 
OBJECT_NAME 
 
-------------------------------------------------------------------- 
 
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE 
 
------------------------------ ---------- -------------- ----------- 
 
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S 
 
------------ ------------ ------------------- ------- - - - 
 
SYS 
 
STANDARD 
 
888 PACKAGE 
 
19-APR-10 19-APR-10 2003-04-18:00:00:00 VALID N N N 
 
OWNER 
 
------------------------------ 
 
OBJECT_NAME 
 
-------------------------------------------------------------------- 
 
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE 
 
------------------------------ ---------- -------------- ------------------- 
 
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S 
 
------------ ------------ ------------------- ------- - - - 
 
SYS 
 
STANDARD 
 
889 PACKAGE BODY 
 
19-APR-10 19-APR-10 2010-04-19:10:22:58 VALID N N N 

3.查詢上面查詢的執(zhí)行計(jì)劃

SQL> set autotrace traceonly explain 
 
SQL> select * from test where object_name='STANDARD'; 
 
Execution Plan 
 
---------------------------------------------------------- 
 
Plan hash value: 1357081020 
 
-------------------------------------------------------------------------- 
 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
 
-------------------------------------------------------------------------- 
 
| 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | 
 
|* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | 
 
-------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
 
--------------------------------------------------- 
 
1 - filter("OBJECT_NAME"='STANDARD') 
 
Note 
 
----- 
 
- dynamic sampling used for this statement 

4.在表test的object_name列上創(chuàng)建一個(gè)虛擬索引

SQL> create index test_index on test(object_name) nosegment;Index created. 

為了創(chuàng)建虛擬索引必須在create index語句中指定nosegment子句，并且不會創(chuàng)建索引段。

5.來驗(yàn)證虛擬索引不會創(chuàng)建索引段

SQL> set autotrace off 
 
SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX'; 
 
no rows selected 
 
SQL> col OBJECT_NAME format a20; 
 
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX'; 
 
OBJECT_NAME OBJECT_TYPE 
 
-------------------- ------------------- 
 
TEST_INDEX INDEX 

從上面的結(jié)果可以看到索引對象已經(jīng)創(chuàng)建，但沒有創(chuàng)建索引段。

6.重新執(zhí)行sql查看創(chuàng)建的虛擬索引是否被使用

SQL> set autotrace traceonly explainSQL> select * from test where object_name='STANDARD'; 
 
Execution Plan 
 
---------------------------------------------------------- 
 
Plan hash value: 1357081020 
 
-------------------------------------------------------------------- 
 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
 
-------------------------------------------------------------------- 
 
| 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | 
 
|* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | 
 
-------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
 
--------------------------------------------------- 
 
1 - filter("OBJECT_NAME"='STANDARD') 
 
Note 
 
----- 
 
- dynamic sampling used for this statement 

從上面的執(zhí)行計(jì)劃可以清楚地看到創(chuàng)建的虛擬索引并沒有被使用。

7.為了能使用所創(chuàng)建的虛擬索引，需要將_USE_NOSEGMENT_INDEXES設(shè)置為true

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;Session altered. 

8.重新執(zhí)行sql查看創(chuàng)建的虛擬索引是否被使用

SQL> set long 900SQL> set linesize 900 
 
SQL> select * from test where object_name='STANDARD'; 
 
Execution Plan 
 
---------------------------------------------------------- 
 
Plan hash value: 2627321457 
 
-------------------------------------------------------------------- 
 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
 
-------------------------------------------------------------------- 
 
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 | 
 
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 | 
 
|* 2 | INDEX RANGE SCAN | TEST_INDEX | 238 | | 1 (0)| 00:00:01 | 
 
-------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
 
--------------------------------------------------- 
 
2 - access("OBJECT_NAME"='STANDARD') 
 
Note 
 
----- 
 
- dynamic sampling used for this statement 

從上面的執(zhí)行計(jì)劃可以看到當(dāng)設(shè)置隱含參數(shù)_USE_NOSEGMENT_INDEXES后，優(yōu)化器將會使用創(chuàng)建的虛擬索引。在使用虛擬索引需要注意，我們可以分析虛擬索引，但不能重建虛擬索引，如果重建虛擬索引會收到ORA-8114: "User attempted to alter a fake index"錯(cuò)誤提示，可以刪除虛擬索引。