【DB筆試面試556】在Oracle中，虛擬索引的作用有哪些？

  • 2019 年 10 月 10 日
  • 筆記

♣

題目部分

在Oracle中，虛擬索引的作用有哪些？

♣

答案部分

在資料庫優化中，索引的重要性是不言而喻的。但是，在性能調整過程中，一個索引是否能被查詢用到，在索引創建之前是無法確定的，而創建索引是一個代價比較高的操作，尤其是當數據量較大的時候。這種情況下，創建虛擬索引是一個很好的選擇。

虛擬索引（Virtual Index）是定義在數據字典中的偽索引，但沒有相關的索引段。虛擬索引的目的是模擬索引的存在而不用真實的創建一個完整索引。這允許開發者創建虛擬索引來查看相關執行計劃而不用等到真實創建完索引才能查看索引對執行計劃的影響，並且不會增加存儲空間的使用。需要確保創建的索引將不會對資料庫中的其它查詢產生負面影響，這些都可以使用虛擬索引來完成測試。

虛擬索引與不可見索引的不同之處在於不可見索引是有與之相關的存儲的，只是優化器不能選擇它們。而虛擬索引沒有與之相關的存儲空間。由於這個原因，虛擬索引也被稱為無段索引。

Oracle文檔中並沒有提到虛擬索引的創建語法，實際上就是普通索引語法後面加一個NOSEGMENT關鍵字即可，B-Tree索引和BITMAP索引都可以被創建成虛擬索引。

需要注意的是，必須設置隱含參數「_USE_NOSEGMENT_INDEXES」為TRUE（默認為FALSE）後，CBO（Cost Based Optimization，基於代價的優化器）模式才能使用虛擬索引，而RBO（Rule Based Optimization，基於規則的優化器）模式無法使用虛擬索引。

可以使用如下的SQL語句查找系統中已經存在的虛擬索引：

SELECT INDEX_OWNER, INDEX_NAME    FROM DBA_IND_COLUMNS   WHERE INDEX_NAME NOT LIKE 'BIN$%'  MINUS  SELECT OWNER, INDEX_NAME    FROM DBA_INDEXES;

關於虛擬索引需要注意以下幾點：

①　虛擬索引無法執行ALTER INDEX操作。

SQL> ALTER INDEX IX_T_ID REBUILD;  ALTER INDEX IX_T_ID REBUILD*  第 1 行出現錯誤:  ORA-08114: 無法變更假索引

②　使用回收站特性的時候，虛擬索引必須顯式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                   BIN$7jAFlUG6b1zgQAB/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: 此列列表已索引

③　不能創建和虛擬索引同名的實際索引。

④　可以創建和虛擬索引包含相同列但不同名的實際索引。

⑤　虛擬索引可以被分析並且有效，但是數據字典里查不到結果。

下面給出虛擬索引的一個示例：

SYS@lhrdb> SELECT * FROM V$VERSION WHERE ROWNUM<=2;  BANNER  --------------------------------------------------------------------------------  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  PL/SQL Release 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.      --從數據字典DBA_INDEXES中是無法找到這個索引的，但是DBA_OBJECTS的確存在：  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> 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性能的影響，該怎麼辦？」，那麼答案就是要麼在測試庫創建索引來測試，要麼使用虛擬索引來測試性能。

& 說明：

有關虛擬索引的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2123687/

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。

VirMach 便宜 VPS

QNews

QNews