Oracle之虛擬索引
Oracle之虛擬索引
1 BLOG文件結構圖
2 前言部分
2.1 導讀和注意事項
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① Oracle虛擬索引的使用
Tips：
① 本文在ITpub
（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）
③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，可以去部落格園地址閱讀
④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
3 虛擬索引(Virtual Index)
傳統的效能最佳化和調整工作，大都是在系統上線之後，由運維團隊進行的。當系統資料量積累到一定程度之後，原有一些隱藏的問題就不斷出現。所以，在大資料量、應急場景下進行SQL調優，往往是運維團隊經常遇到的問題。新增索引是我們經常使用的效能最佳化手段。在遇到問題的時候，試一試新增索引，看看能不能改變執行計劃，是我們分析和解決問題的過程手段。但是對於大資料表情況下，快速的建立索引是比較困難的事情。這個時候，我們可以利用Oracle的virtual index技術。
在資料庫最佳化中，索引的重要性不言而喻。但是，在效能調整過程中，一個索引是否能被查詢用到，在索引建立之前是無法確定的，而建立索引是一個代價比較高的操作，尤其是資料量較大的時候。
虛擬索引(Virtual Index)是定義在資料字典中的偽索引，但沒有相關的索引段。虛擬索引的目的是模擬索引的存在而不用真實的建立一個完整索引。這允許開發者建立虛擬索引來檢視相關執行計劃而不用等到真實建立完索引才能檢視索引對執行計劃的影響，並且不會增加儲存空間的使用。如果我們觀察到最佳化器生成了一個昂貴的執行計劃並且SQL調整指導建議我們對某些的某列建立索引，但在生產資料庫環境中建立索引與測試並不總是可以操作。我們需要確保建立的索引將不會對資料庫中的其它查詢產生負面影響，因此可以使用虛擬索引。
虛擬索引不是物理存在的，它並不會建立實際的索引段，只是在資料字典中加了一個索引的記錄，使得最佳化器能夠意識到一個索引的存在，從而判斷是否使用該索引作為訪問路徑。當然，實際上最終查詢的訪問路徑是不會使用該虛擬索引的。所以，虛擬索引的用處就是用來判斷一個索引對於SQL的執行計劃的影響，尤其是對整個資料庫的影響，從而判斷是否需要建立物理索引。可以將這個索引刪掉並重建常規索引。
虛擬索引與不可見索引的不同之處在於不可見索引是有與之相關的儲存的，只是最佳化器不能選擇它們。而虛擬索引沒有與之相關的儲存空間。由於這個原因，虛擬索引也被稱為無段索引。
Oracle文件中並沒有提到虛擬索引的建立語法，實際上就是普通索引語法後面加一個NOSEGMENT關鍵字即可，B*TREE INDEX和BITMAP INDEX都可以。
必須設定隱含引數"_USE_NOSEGMENT_INDEXES"=TRUE（預設為FALSE)後CBO最佳化器模式才能使用虛擬索引，RBO最佳化器模式無法使用虛擬索引。
--建立虛擬索引，首先要將_use_nosegment_indexes的隱含引數設定為true
SQL> alter session set "_use_nosegment_indexes"=true;
--虛擬索引的建立語法比較簡單，實際上就是普通索引語法後面加一個nosegment關鍵字
SQL> create index ix_t_id on t(object_id) nosegment;
索引已建立。
Oracle Virtual Index是一個研究工具，是我們在投產環境上繼續SQL最佳化方案研究時候的不錯工具。它既滿足了讓我們建立索引，看執行計劃效果的需求。同時也不會消耗很多的索引build資源。
查詢系統中已經存在的虛擬索引：
SELECT INDEX_OWNER, INDEX_NAME
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME NOT LIKE 'BIN$%'
MINUS
SELECT OWNER, INDEX_NAME
FROM DBA_INDEXES;
3.1 虛擬索引型別及特點
虛擬索引支援B-TREE索引和BIT點陣圖索引，在CBO模式下ORACLE最佳化器會考慮虛擬索引，但是在RBO模式下需要新增hint才行。
同樣的資料量，若使用nosegment虛擬索引使用的時間很短。
1. 虛擬索引無法執行alter index選項
SQL> alter index IX_T_ID rebuild;
alter index IX_T_ID rebuild*
第 1 行出現錯誤:
ORA-08114: 無法變更假索引
2. 使用回收站特性的時候，虛擬索引必須顯示drop，才能建立同名的索引。
SQL> create index ind_status on t(status);
索引已建立。
SQL> drop table t;
表已刪除。
SQL> flashback table t to before drop;
閃回完成。
SQL> select table_name,index_name,status from user_indexes where table_name='T';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
T BIN7jAFlUG6b1zgQAB/AQAPyw==0 VALID
SQL> create index ind_object_id on t(object_id);
索引已建立。
SQL> create index inds_status on t(status);create index inds_status on t(status)
*
第 1 行出現錯誤:
ORA-01408: 此列列表已索引
3. 不能建立和虛擬索引同名的實際索引；
4. 可以建立和虛擬索引包含相同列但不同名的實際索引；
5. 虛擬索引分析並且有效，但是資料字典裡查不到結果。
4 實驗部分
4.1 個人示例
SYS@lhrdb> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SYS@lhrdb> CREATE TABLE T_VI_20160818_01_LHR AS SELECT * FROM DBA_OBJECTS;
Table created.
虛擬索引的建立語法比較簡單，實際上就是普通索引語法後面加一個nosegment關鍵字
SYS@lhrdb> CREATE INDEX IX_VI01_ID ON T_VI_20160818_01_LHR(OBJECT_ID) NOSEGMENT;
Index created.
從資料欄位中是無法找到這個索引的。
SYS@lhrdb> SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='T_VI_20160818_01_LHR';
no rows selected
SYS@lhrdb> COL OBJECT_NAME FORMAT A10
SYS@lhrdb> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_TYPE FROM DBA_OBJECTS D WHERE D.OBJECT_NAME='IX_VI01_ID';
OWNER OBJECT_NAM OBJECT_TYPE
------------------------------ ---------- -------------------
SYS IX_VI01_ID INDEX
SYS@lhrdb> SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID')) FROM DUAL;
TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID'))
-------------------------------------------------------------------
CREATE INDEX "SYS"."IX_VI01_ID" ON "SYS"."T_VI_20160818_01_LHR" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOSEGMENT
使用虛擬索引，首先要將_use_nosegment_indexes的隱含引數設定為true
SYS@lhrdb> ALTER SESSION SET "_USE_NOSEGMENT_INDEXES"=TRUE;
Session altered.
SYS@lhrdb> SHOW PARAMETER optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SYS@lhrdb> SET AUTOTRACE TRACEONLY
SYS@lhrdb> SET LINE 9999
SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3209519479
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2898 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_VI_20160818_01_LHR | 14 | 2898 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_VI01_ID | 312 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1249 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SYS@lhrdb>
以下看的是真實執行計劃，顯然是用不到索引。
SYS@lhrdb> SET AUTOTRACE OFF
SYS@lhrdb> ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;
no rows selected
SYS@lhrdb> SELECT SQL_ID,CHILD_NUMBER,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ---------------------------------------------------
d5v59m8vyyz7d 0 SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1
SYS@lhrdb> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('d5v59m8vyyz7d',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID d5v59m8vyyz7d, child number 0
-------------------------------------
SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1
Plan hash value: 847945500
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1249 |
|* 1 | TABLE ACCESS FULL| T_VI_20160818_01_LHR | 1 | 14 | 0 |00:00:00.01 | 1249 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
查詢系統中已經存在的虛擬索引：
SYS@lhrdb> SELECT INDEX_OWNER, INDEX_NAME
2 FROM DBA_IND_COLUMNS
3 WHERE INDEX_NAME NOT LIKE 'BIN$%'
4 MINUS
5 SELECT OWNER, INDEX_NAME
6 FROM DBA_INDEXES;
INDEX_OWNER INDEX_NAME
------------------------------ ------------------------------
SYS IX_VI01_ID
虛擬索引是定義在資料字典中的偽索引，但沒有相關的索引段。虛擬索引的目的是模擬索引的存--而不用真實的建立一個完整索引。這允許開發者建立虛擬索引來檢視相關執行計劃而不用等到真實建立完索引才能檢視索引對執行計劃的影響，並且不會增加儲存空間的使用。如果我們觀察到最佳化器生成了一個昂貴的執行計劃並且SQL調整指導建議我們對某些的某列建立索引，但在生產資料庫環境中建立索引與測試並不總是可以操作。我們需要確保建立的索引將不會對資料庫中的其它查詢產生負面影響，因此可以使用虛擬索引。
下面舉例進行說明
1.建立一個測試表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.查詢上面查詢的執行計劃
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列上建立一個虛擬索引
SQL> create index test_index on test(object_name) nosegment; Index created.
為了建立虛擬索引必須在create index語句中指定nosegment子句，並且不會建立索引段。
5.來驗證虛擬索引不會建立索引段
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
從上面的結果可以看到索引物件已經建立，但沒有建立索引段。
6.重新執行sql檢視建立的虛擬索引是否被使用
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
從上面的執行計劃可以清楚地看到建立的虛擬索引並沒有被使用
7.為了能使用所建立的虛擬索引，需要將_USE_NOSEGMENT_INDEXES設定為true
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true; Session altered.
8.重新執行sql檢視建立的虛擬索引是否被使用
SQL> set long 900
SQL> 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
從上面的執行計劃可以看到當設定隱含引數_USE_NOSEGMENT_INDEXES後，最佳化器將會使用建立的虛擬索引。在使用虛擬索引需要注意，我們可以分析虛擬索引，但不能重建虛擬索引，如果重建虛擬索引會收到ORA-8114: "User attempted to alter a fake index"錯誤提示，可以刪除虛擬索引。
傳統的效能最佳化和調整工作，大都是在系統上線之後，由運維團隊進行的。當系統資料量積累到一定程度之後，原有一些隱藏的問題就不斷出現。所以，在大資料量、應急場景下進行SQL調優，往往是運維團隊經常遇到的問題。
新增索引是我們經常使用的效能最佳化手段。在遇到問題的時候，試一試新增索引，看看能不能改變執行計劃，是我們分析和解決問題的過程手段。但是對於大資料表情況下，快速的建立索引是比較困難的事情。這個時候，我們可以利用Oracle的virtual index技術。
1、環境介紹和資料準備
Virtual Index出現的很早。筆者從9i時候的文件資料中，就可以看到virtual index的技術材料。我們還是選擇Oracle 11gR2進行試驗。
SQL> select * from v$version;
BANNER
----------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
我們建立資料表T作為實驗物件，同時建立正常Index和虛擬Index。
SQL> show user;
User is "scott"
SQL> create table t as select * from dba_objects;
Table created
SQL> set timing on;
--建立一個普通索引
SQL> create index idx_t_owner on t(owner);
Index created
Executed in 0.687 seconds
SQL> select count(*) from t;
COUNT(*)
----------
72792
Executed in 0.015 seconds
我們建立virtual index，需要使用nosegment關鍵字。
SQL> create index idx_t_obj on t(object_id) nosegment;
Index created
Executed in 0.047 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 1.716 seconds
此處我們需要注意一個細節，同樣是在7萬多基礎資料上面建立索引。nosegment虛擬索引使用的時間很短。
2、資料字典層面看virtual index
我們建立了虛擬索引idx_t_obj，又建立了作為參照的idx_t_owner。下面可以從資料字典的層面，去看看虛擬索引的內容資訊。
Oracle所有索引資訊都記錄在dba_indexes檢視中。
SQL> select index_name, index_type from dba_indexes where wner='SCOTT' and table_name='T';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IDX_T_OWNER NORMAL
Executed in 0.031 seconds
SQL> select segment_name from dba_segments where wner='SCOTT' and segment_name in ('IDX_T_OWNER','IDX_T_OBJ');
SEGMENT_NAME
--------------------------------------------------------------------
IDX_T_OWNER
Executed in 0.062 seconds
我們從dba_indexes和dba_segments中，都只能看到普通索引idx_t_owner的資訊。而建立的虛擬索引idx_t_obj沒有蹤跡。nosegment選項可以讓我們猜測是沒有索引段物件的建立過程。但是，作為字典的dba_indexes資訊沒有，就讓人疑惑。
驗證我們的想法，使用dbms_metadata.get_ddl方法，抽取到資料表t的字典定義。其中，我們看到了idx_t_obj的資訊。
CREATE INDEX "SCOTT"."IDX_T_OBJ" ON "SCOTT"."T" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOSEGMENT ;
CREATE INDEX "SCOTT"."IDX_T_OWNER" ON "SCOTT"."T" ("OWNER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
相對於idx_t_owner，虛擬索引的定義全文顯得很簡單，只有nosegment很顯眼。
那麼，作為萬物彙總的dba_objects中呢？
SQL> select owner,object_name, object_id, data_object_id, object_type from dba_objects where object_name in ('IDX_T_OWNER','IDX_T_OBJ');
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
----- --------------- ---------- -------------- -------------------
SCOTT IDX_T_OWNER 78019 78019 INDEX
SCOTT IDX_T_OBJ 78020 78020 INDEX
Executed in 0.047 seconds
在dba_objects中，我們找到idx_t_obj的資訊，它依然被認為是一個索引。更重要的是，我們定位到了object_id和data_object_id，這兩個分別為資料庫物件的邏輯id和物理段id。
dba_indexes字典檢視的基礎資料表是ind$基表。其中定義了所有索引物件的資訊。我們藉助object_id去檢查，發現了無法查詢到的idx_t_obj物件記錄。
SQL> select obj#, ts#, file#, block#, bo# from ind$ where obj# in (78019, 78020);
OBJ# TS# FILE# BLOCK# BO#
---------- ---------- ---------- ---------- ----------
78019 4 4 1586 78017
78020 4 0 0 78017
Executed in 0.015 seconds
SQL> select owner, object_name from dba_objects where object_id=78017;
OWNER OBJECT_NAME
----- ---------------
SCOTT T
Executed in 0.016 seconds
我們可以從bo#編號，確定的確是資料表scott.t的索引物件。那麼，我們思考一個問題，既然ind$中存在對應記錄，為什麼dba_indexes不能檢索到這個資訊呢？
透過抽取dba_indexes的原始碼資訊，我們可以猜到端倪。
from sys.ts$ ts, sys.seg$ s,
sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o,
sys.user$ itu, sys.obj$ ito, sys.deferred_stg$ ds
where u.user# = o.owner#
and o.obj# = i.obj#
and i.bo# = io.obj#
and io.owner# = iu.user#
and bitand(i.flags, 4096) = 0
and bitand(o.flags, 128) = 0
and i.ts# = ts.ts# (+)
and i.file# = s.file# (+)
and i.block# = s.block# (+)
and i.ts# = s.ts# (+)
and i.obj# = ds.obj# (+)
and i.indmethod# = ito.obj# (+)
and ito.owner# = itu.user# (+);
雖然虛擬索引是沒有段的，在seg$中必然沒有對應記錄。但是SQL語句中對於這個條件定義的是外連線。也就是說，即使沒有段結構，索引也能顯示出來。
疑點就落在對一些列flag標記的bitand操作上了。我們檢查一下ind$的基礎flags取值，就可以知道原因了。
SQL> select obj#, bitand(flags, 4096) from ind$ where obj# in (78019, 78020);
OBJ# BITAND(FLAGS,4096)
---------- ------------------
78019 0
78020 4096
Executed in 0.016 seconds
看來，雖然ind$中包括資訊，但是不顯示出來，也是Oracle的一個本意。
下面我們繼續來看virtual index的實際工作效果。
3、“不成功”的實驗
Virtual index的特點就是沒有段segment結構的支援，在資料字典的基表中存在痕跡。那麼，它對於我們的執行計劃有什麼樣的影響呢？
這裡我們需要區分兩個概念，就是執行計劃SEP的生成和執行。Oracle最佳化器是一個獨立的元件，是可以單獨進行工作的。同時，Oracle執行計劃真正的情況，是從Shared Pool中抽取出來的。
Virtual index沒有segment結構支援，所以根本不可能實際去執行，即使最佳化器命令走virtual index路徑。那麼，我們從執行計劃和實際執行兩個角度看問題。
首先，我們不做任何額外的配置，看看在virtual index存在的情況下，預設情況下會給我們帶來什麼。
--反映Oracle Optimizer的判定；
SQL> explain plan for select * from t where object_id = 10000;
Explained
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 273 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 97 | 273 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10000)
13 rows selected
Executed in 0.109 seconds
Explain plan for是最佳化器單獨工作，SQL是不真正執行的！看來virtual index不會在這個時候影響最佳化器。
那麼，執行時如何？我們先執行SQL，從shared pool中抽取sql_id資訊。
SQL> select /*+demo*/count(*) from t where object_id=10000;
COUNT(*)
----------
1
Executed in 0.078 seconds
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/count(*)%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
d2s9wnt37f4g7 1 1
利用dbms_xplan包進行抽取。
SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'd2s9wnt37f4g7'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID d2s9wnt37f4g7, child number 0
-------------------------------------
select /*+demo*/count(*) from t where object_id=10000
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 5 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=10000)
19 rows selected
實際執行的SQL中，也沒有執行virtual index路徑。所以：在預設的情況下，virtual index既不會參與單獨的Optimizer決定，也不會生成與virtual index有關的真實執行計劃來執行。
4、“受到影響”的最佳化器
要讓virtual index起作用，需要調整一個Oracle隱含引數_use_nosegment_indexes。預設這個引數取值為false，表示不開啟nosegment indexes功能。
我們可以在instance和session兩個level去設定這個引數。
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered
Executed in 0 seconds
我們再來看剛剛的實驗。
--explain plan for命令
SQL> explain plan for select * from t where object_id = 10000;
Explained
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2999300365
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 0
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 0
|* 2 | INDEX RANGE SCAN | IDX_T_OBJ | 1 | | 1 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10000)
14 rows selected
Executed in 0.078 seconds
我們發現，單獨呼叫optimizer工作的時候，virtual index路徑被走到了。那麼，真實執行呢？
--真正去執行一下
SQL> select /*+demo_2*/count(*) from t where object_id=10000;
COUNT(*)
----------
1
Executed in 0.015 seconds
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo_2*/count(*)%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
8gbx9grs6cga2 1 1
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '8gbx9grs6cga2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8gbx9grs6cga2, child number 0
-------------------------------------
select /*+demo_2*/count(*) from t where object_id=10000
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 5 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=10000)
19 rows selected
Executed in 0.078 seconds
從實際情況看，設定了隱含引數後，單獨CBO進行執行計劃判定的時候，是會考慮nosegment索引的。但是，在真正執行的時候，還是不會考慮virtual index，因為這個索引並不存在，也不能支援真正執行。
5、CBO or RBO
此時，筆者想到一個問題。Oracle CBO在工作的時候，索引路徑只是執行計劃的一種“可選路徑”。究竟是FTS（Full Table Scan）還是Index Path，取決於統計量計算出的成本值。
那麼，virtual index在工作的時候，沒有段結構與之對應，統計量也必然有一些不完全。那麼，Oracle在生成執行計劃的時候，是否進行CBO判定呢？
一個最簡單的方法，就是偏移列索引路徑判定。
--構造偏移列
SQL> update t set wner='SYS' where owner <> 'SCOTT';
72773 rows updated
Executed in 7.628 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
刪除原有的owner列一般索引，建立nosegment索引。
SQL> drop index idx_t_owner;
Index dropped
Executed in 0.234 seconds
SQL> create index idx_t_owner on t(owner) nosegment;
Index created
Executed in 0.078 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 1.123 seconds
--不存在index正式結論；
SQL> select count(*) from dba_indexes where wner='SCOTT' and index_name='IDX_T_OWNRE';
COUNT(*)
----------
0
Executed in 0.015 seconds
測試對owner列的選擇執行計劃。
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered
Executed in 0 seconds
--小數值執行計劃
SQL> explain plan for select * from t where wner='SCOTT';
Explained
Executed in 0.015 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1235 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 13 | 1235 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 13 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
14 rows selected
Executed in 0.031 seconds
--大數值偏移執行計劃
SQL> explain plan for select * from t where wner='SYS';
Explained
Executed in 0 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72772 | 6751K| 273 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 72772 | 6751K| 273 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
13 rows selected
Executed in 0.063 seconds
看來，在工作中的確是CBO成本運算。對於一些不存在的統計值，Oracle可能是選擇一個預設值來定義計算。
6、結論
Oracle Virtual Index是一個研究工具，是我們在投產環境上繼續SQL最佳化方案研究時候的不錯工具。它既滿足了讓我們建立索引，看執行計劃效果的需求。同時也不會消耗很多的索引build資源。
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2123687/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能優化之虛擬索引Oracle優化索引
- Oracle虛擬索引Oracle索引
- 【索引】Oracle之不可見索引和虛擬索引的比對索引Oracle
- Oracle效能最佳化之虛擬索引Oracle索引
- Oracle中的虛擬列索引-nosegment indexOracle索引Index
- 介紹Oracle Virtual Index虛擬索引（上）OracleIndex索引
- oracle11g新特性之--虛擬列Oracle
- 關於openGauss中的虛擬索引索引
- 連線虛擬機器oracle 和虛擬機器KEY虛擬機Oracle
- 網路虛擬化之linux虛擬網路基礎Linux
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式
- MySQL5.7 虛擬列實現表示式索引MySql索引
- oracle之 反向鍵索引Oracle索引
- Oracle之不可見索引Oracle索引
- Oracle之函式索引Oracle函式索引
- 藉助 VMware 虛擬化 OracleOracle
- oracle 11g 虛擬列Oracle
- 虛擬化技術之kvm虛擬機器建立工具qemu-kvm虛擬機
- 虛擬機器 之 Fedora Core 5.0 用 Xen 虛擬Slackware 10.2虛擬機
- Oracle11新特性——虛擬列Oracle
- ORACLE DISCOVERER虛擬記憶體低Oracle記憶體
- Oracle筆記 之 索引(index)Oracle筆記索引Index
- Oracle 10g 新特性之虛擬專用資料庫(轉)Oracle 10g資料庫
- nginx之 nginx虛擬機器配置Nginx虛擬機
- 探索c#之虛擬桶分片C#
- 虛擬機器之介紹_2.0虛擬機
- 虛擬化技術之kvm虛擬機器建立工具virt-install虛擬機
- 深入理解虛擬機器之虛擬機器類載入機制虛擬機
- oracle全文索引之STOPLIST_ CTXCAT 索引_INDEX SETOracle索引Index
- oracle 虛擬專用資料庫（VPD）Oracle資料庫
- Oracle11新特性——虛擬列（二）Oracle
- Oracle全文檢索之Ctxcat 索引Oracle索引
- oracle全文索引之STORAGE PREFERENCEOracle索引
- oracle全文索引之WORDLIST PREFERENCEOracle索引
- 【原創】Linux虛擬化KVM-Qemu分析（四）之CPU虛擬化（2）Linux
- 深入理解虛擬機器之虛擬機器位元組碼執行引擎虛擬機
- 虛擬化技術之kvm基礎
- Flutter之Dart虛擬機器啟動FlutterDart虛擬機