【DB筆試面試553】在Oracle中，什麼是不可見索引？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，什麼是不可見索引？
♣
答案部分
索引維護是DBA的一項重要工作。當一個系統運行很長一段時間，經過需求變更、結構設計變化後，系統中就可能會存在一些不再被使用的索引，或者使用效率很低的索引。這些索引的存在，不僅佔用系統空間，而且會降低事務效率，增加系統的負載。因此，需要找出那些無用或低效的索引，並刪除它們（找出無用索引可以通過索引監控的方法）。但是，直接刪除索引還是存在一定風險的。例如，某些索引可能只是在一些周期的作業中被使用到，而如果監控周期沒有覆蓋到這些作業的觸發點，那麼就會認為索引是無用的，從而將其刪除。當作業啟動後，可能就會對系統性能造成衝擊。這時，可能就會手忙腳亂地去找回索引定義語句、重建索引。在Oracle 11g里，Oracle提供了一個新的特性來降低直接刪除索引或者禁用索引的風險，那就是不可見索引（Invisible Indexes）。
從Oracle 11g開始，可以創建不可見索引。優化程式會忽略不可見索引，除非在會話或系統級別上將OPTIMIZER_USE_INVISIBLE_INDEXES初始化參數顯式設置為TRUE，此參數的默認值是FALSE。
使索引不可見是使索引不可用或被刪除的一種替代方法。使用不可見索引，可以完成以下操作：
（1）在刪除索引之前測試對索引刪除後對系統性能的影響。
（2）對應用程式的特定操作或模組使用臨時索引結構，這樣就不會影響整個應用程式了。
當索引不可見時，優化程式生成的計劃不會使用該索引。如果未發現性能下降，那麼可以刪除該索引。還可以創建最初不可見索引，執行測試，然後確定是否使該索引可見。可以查詢DBA_INDEXES數據字典視圖的VISIBILITY列來確定該索引是VISIBLE還是INVISIBLE。
創建不可見索引的方式如下所示：
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) INVISIBLE;
修改索引是否可見的方式如下所示：
ALTER INDEX INDEX_NAME INVISIBLE; --修改索引不可見 ALTER INDEX INDEX_NAME VISIBLE; --修改索引可見
不可見索引的特點主要有以下幾點：
（1）當索引變更為不可見的時候，只是對Oracle的優化器不可見。
（2）不可見索引在DML操作的時候也會被維護。
（3）加HNIT對不可見索引無效。
（4）可以通過修改SYSTEM級別和SESSION級別參數來使用不可見索引。
不可見索引是從Oracle 11g開始出現的，所以，在Oracle 11g之前的版本中索引沒有INVISIBLE的功能，那麼應該如何處理呢？有2種辦法，第一，讓索引變為UNUSABLE；第二，修改索引的統計資訊。
在Oracle 11g之前，可以先不刪除索引，而將其修改為UNUSABLE。這樣的話，索引的定義並未刪除，只是索引不能再被使用，也不會隨著表數據的更新而更新。當需要重新使用該索引時，需要用REBUILD語句重建、然後更新統計資訊。對於一些大表來說，這個時間可能就非常長。
現在Oracle資料庫一般都採用基於成本的優化器來生成執行計劃，只要索引的成本更低，Oracle就會選擇使用索引，所以，只要告訴Oracle使用索引成本很高，它就不會使用這個索引，這樣就達到了暫時讓索引不可用的效果。Oracle提供了DBMS_STATS包來管理對象的統計資訊，通過DBMS_STATS.SET_INDEX_STATS函數可以強制設置索引的統計資訊，現在只要把索引的成本設置成非常大即可。
設置非常離譜的統計資訊，讓Oracle認為使用索引的成本很高：
SYS@lhrdb> SELECT A.OWNER,A.INDEX_NAME,A.BLEVEL,A.LEAF_BLOCKS,A.NUM_ROWS FROM DBA_INDEXES A WHERE INDEX_NAME='IDX_II_20160819'; OWNER INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS ------------------------------ ------------------------------ ---------- ----------- ---------- SYS IDX_II_20160819 1 193 87133 SYS@lhrdb> EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME => user,INDNAME => 'IDX_II_20160819',INDLEVEL => 10,NUMLBLKS => 1000000000,NUMROWS => 100000000000,NO_INVALIDATE => FALSE ); PL/SQL procedure successfully completed. SYS@lhrdb> col NUM_ROWS format 999999999999999 SYS@lhrdb> SELECT A.OWNER,A.INDEX_NAME,A.BLEVEL,A.LEAF_BLOCKS,A.NUM_ROWS FROM DBA_INDEXES A WHERE INDEX_NAME='IDX_II_20160819'; OWNER INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS ------------------------------ ------------------------------ ---------- ----------- ---------------- SYS IDX_II_20160819 10 1000000000 100000000000
其中，NO_INVALIDATE=FALSE表示讓Library Cache中的執行計劃立即失效，重新按現在的統計資訊生成SQL執行計劃。
虛擬索引和不可見索引的區別如表 3-18所示。
表 3-18 虛擬索引和不可見索引的區別
|
比較項目 |
不可見索引（Invisible Indexes） |
虛擬索引（Virtual Indexes，無段索引） |
|---|---|---|
|
出現版本 |
Oracle 11g |
Oracle 9i |
|
有無索引段 |
有索引段，佔用一定的存儲空間 |
無索引段，不佔用存儲空間 |
|
是否可以通過ALTER直接切換其屬性 |
可以通過ALTER直接修改索引是否可見：ALTER INDEX INDEX_NAME INVISIBLE;ALTER INDEX INDEX_NAME VISIBLE; |
不能通過ALTER修改屬性，也不能通過ALTER重建虛擬索引 |
|
視圖DBA_INDEXES是否可以查詢到 |
是 |
否 |
|
視圖DBA_OBJECTS是否可以查詢到 |
是 |
是 |
|
啟用參數 |
OPTIMIZER_USE_INVISIBLE_INDEXES（默認為FALSE） |
_USE_NOSEGMENT_INDEXES（默認為FALSE） |
|
創建語法 |
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) INVISIBLE; |
CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) NOSEGMENT; |
|
查詢系統中存在的所有不可見或虛擬索引的SQL |
SELECT OWNER, INDEX_NAME FROM DBA_INDEXES WHERE VISIBILITY='INVISIBLE'; |
SELECT INDEX_OWNER, INDEX_NAME FROM DBA_IND_COLUMNS WHERE INDEX_NAME NOT LIKE 'BIN$%'MINUSSELECT OWNER, INDEX_NAME FROM DBA_INDEXES; |
|
作用 |
當索引不可見時，優化程式生成的計劃不會使用該索引。如果未發現性能下降，那麼可以刪除該索引。還可以創建最初不可見索引，執行測試，然後確定是否使該索引可見 |
模擬索引的存在而不用真實的創建一個完整索引。這允許開發者創建虛擬索引來查看相關執行計劃而不用等到真實創建完索引才能查看索引對執行計劃的影響，並且不會增加存儲空間的使用 |
|
共同點 |
都可以通過參數在SESSION和SYSTEM級別進行設置 |
|
下面給出不可見索引的使用示例：
創建表、不可見索引，並收集統計資訊：
SYS@lhrdb> CREATE TABLE T_II_20160819_01_LHR AS SELECT * FROM DBA_OBJECTS; Table created. SYS@lhrdb> CREATE INDEX IDX_II_20160819 ON T_II_20160819_01_LHR(OBJECT_ID) INVISIBLE; Index created. SYS@lhrdb> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_II_20160819'; VISIBILIT --------- INVISIBLE SYS@lhrdb> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>USER,TABNAME=>'T_II_20160819_01_LHR',DEGREE=>2,CASCADE => TRUE); PL/SQL procedure successfully completed. --帶WHERE條件查詢： SYS@lhrdb> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE SYS@lhrdb> set line 9999 SYS@lhrdb> set autot traceonly exp SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1; Execution Plan ---------------------------------------------------------- Plan hash value: 700947541 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 98 | 343 (2)| 00:00:05 | |* 1 | TABLE ACCESS FULL| T_II_20160819_01_LHR | 1 | 98 | 343 (2)| 00:00:05 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=1) --這裡使用了全表掃描，根據唯一性，這裡應該走索引的，加上Hint試試： SYS@lhrdb> SELECT /*+ index(T IDX_II_20160819)*/ * FROM T_II_20160819_01_LHR T WHERE OBJECT_ID=1; Execution Plan ---------------------------------------------------------- Plan hash value: 700947541 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 98 | 343 (2)| 00:00:05 | |* 1 | TABLE ACCESS FULL| T_II_20160819_01_LHR | 1 | 98 | 343 (2)| 00:00:05 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=1) --對於INVISIBLE的INDEX，使用Hint也沒有用。修改OPTIMIZER_USE_INVISIBLE_INDEXES參數為TRUE，再次查詢： SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE; Session altered. SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1; Execution Plan ---------------------------------------------------------- Plan hash value: 2544197461 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_II_20160819 | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1) --這次使用了索引。關閉OPTIMIZER_USE_INVISIBLE_INDEXES參數，將索引改成VISIBLE，再測試： SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE; Session altered. SYS@lhrdb> ALTER INDEX IDX_II_20160819 VISIBLE; Index altered. SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1; Execution Plan ---------------------------------------------------------- Plan hash value: 2544197461 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_II_20160819 | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1)
索引可見，優化器就可以使用到索引。
& 說明：
有關不可見索引的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2124044/
真題1、An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:
SQL>CREATE INDEX ord_custname_ix ON orders(custname);
The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause.You want to check the impact on the performance of the queries if the index is not available.You do not want the index to be dropped or rebuilt to perform this test.
Which is the most efficient method of performing this task?
A、disabling the indexB、making the index invisible
C、aking the index unusableD、using the MONITORING USAGE clause for the index
答案：B。
題目要求在不能刪除和重建的情況下來測試索引的性能。
對於選項A，索引不能被禁用。所以，選項A錯誤。
對於選項B，讓索引不可見，為正確選項。所以，選項B正確。
對於選項C，讓索引不可用之後還是得重建索引。所以，選項C錯誤。
對於選項D，監控索引並不能測試索引在不可用的情況下對系統的性能影響。所以，選項D錯誤。
所以，本題的答案為B。
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
