在數(shù)據(jù)庫優(yōu)化中，索引的重要性不言而喻。但是，在性能調(diào)整過程中，一個(gè)索引是否能被查詢用到，在索引創(chuàng)建之前是無法確定的，而創(chuàng)建索引是一個(gè)代價(jià)比較高的操作，尤其是數(shù)據(jù)量較大的時(shí)候。
虛擬索引（Virtual Index）不是物理存在的，它并不會創(chuàng)建實(shí)際的索引段，只是在數(shù)據(jù)字典中加了一個(gè)索引的記錄，使得優(yōu)化器能夠意識到一個(gè)索引的存在，從而判斷是否使用該索引作為訪問路徑。當(dāng)然，實(shí)際上最終查詢的訪問路徑是不會使用該虛擬索引的。
所以，虛擬索引的用處就是用來判斷一個(gè)索引對于sql的執(zhí)行計(jì)劃的影響，尤其是對整個(gè)數(shù)據(jù)庫的影響，從而判斷是否需要?jiǎng)?chuàng)建物理索引。
oracle文檔中并沒有提到虛擬索引的創(chuàng)建語法，實(shí)際上就是普通索引語法后面加一個(gè)nosegment關(guān)鍵字即可，B*Tree index和bitmap index都可以。
不同版本的虛擬索引的特性可能不一樣，本文的例子執(zhí)行環(huán)境為：
NING@ning>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
1.創(chuàng)建虛擬索引
NING@ning>create table test(id int,name varchar2(30));
Table created.
NING@ning>insert into test select rownum,object_name from all_objects where rownum<1001;
1000 rows created.
NING@ning>commit;
Commit complete.
NING@ning>create unique index ix_test on test(id) nosegment;
Index created.
NING@ning>analyze table test compute statistics;
Table analyzed.
2.使用虛擬索引
NING@ning>explain plan for select * from test where id=1;
Explained.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
必須設(shè)置隱含參數(shù)”_use_nosegment_indexes”=true（默認(rèn)為false)后，CBO才能使用虛擬索引ix_test
NING@ning>alter session set "_use_nosegment_indexes"=true;
Session altered.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 166686173
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IX_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
RBO無法使用虛擬索引
NING@ning>alter session set optimizer_mode=rule;
Session altered.
NING@ning>explain plan for select * from test where id=1;
Explained.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 1357081020
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST |
--------------------------------------------------------------
RBO使用hint可以使用虛擬索引
NING@ning>explain plan for select /*+ index(test,ix_test)*/* from test where id=1;
Explained.
NING@ning>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 166686173
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IX_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
3.虛擬索引的特性
無法執(zhí)行alter index
NING@ning>alter index ix_test rebuild;
alter index ix_test rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index
NING@ning>alter index ix_test rename to ix_test2;
alter index ix_test rename to ix_test2
*
ERROR at line 1:
ORA-08114: can not alter a fake index
不能創(chuàng)建和虛擬索引同名的實(shí)際索引
NING@ning>create index ix_test on test(name);
create index ix_test on test(name)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
可以創(chuàng)建和虛擬索引包含相同列但不同名的實(shí)際索引
NING@ning>create index ix_test2 on test(id);
Index created.
在10g使用回收站特性的時(shí)候，虛擬索引必須顯式drop，或者在drop table后purge table后，才能創(chuàng)建同名的索引
NING@ning>drop table test;
Table dropped.
NING@ning>create unique index ix_test on test2(id);
create unique index ix_test on test2(id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
NING@ning>drop index ix_test;
drop index ix_test
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
NING@ning>purge table test;
Table purged.
NING@ning>create unique index ix_test on test2(id);
Index created.
查找系統(tǒng)中已經(jīng)存在的虛擬索引：
SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;
虛擬索引分析并且有效，但是數(shù)據(jù)字典里查不到結(jié)果，估計(jì)是oracle內(nèi)部臨時(shí)保存了分析結(jié)果