Oracle之不可見索引（invisible indexes）

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皆可，您的批評指正是我寫作的最大動力。

Oracle索引的監控：http://blog.itpub.net/26736162/viewspace-2120752/

oracle 如何預估將要建立的索引的大小：http://blog.itpub.net/26736162/viewspace-1381160/

3 不可見索引(Invisible Indexes)

您常常感到疑惑，索引是否真的有利於使用者的查詢？它可能有利於一個查詢，但會影響 10 個其他查詢。索引肯定會對 INSERT 語句造成負面影響，也會執行潛在的刪除和更新操作，這取決於WHERE 條件是否在索引中包括該列。一個相關的問題是，使用索引時，如果該索引被刪除，會對查詢效能造成什麼影響？當然，您可以刪除索引並檢視對查詢的影響，但說起來容易做起來難。索引實際上如何有助於查詢？您必須重新定義索引，為此，需要進行重新建立。完全重新建立之後，就沒有人能使用它了。重新建立索引也是一個昂貴的過程；它會佔用許多有用的資料庫資源。

我們經常在資料庫上建索引或刪除索引，由於索引對SQL的執行效能影響非常大，有可能變得很好，也有可能變得很差，線上下開發環境我們可以充分測試，對於建立或刪除索引沒什麼問題。但是線上上環境，由於高併發的訪問，如果我們刪除了一個重要的大索引(GB以上)，刪除後才發現大量SQL效能變差，很快主機就LOAD飆升，系統無法執行了，由於索引已經刪除，並且很大，要當場重建基本不可能，因為這個索引巨大，建立估計要幾分鐘甚至幾個小時，況且這時主機已經基本沒有響應，IO全部用光，只能把應用停了，等索引建好後再開始開啟應用，等發生這樣的事才會為自己的失誤而後悔。那我們有沒有辦法讓刪除索引的風險降低呢，答案是有！即本文介紹的不可見索引。

索引維護是DBA的一項重要工作。當一個系統執行很長一段時間，經過需求變更、結構設計變化後，系統中就可能會存在一些不會被使用的索引，或者使用效率很低的索引。這些索引的存在，不僅佔用系統空間，而且會降低事務效率，增加系統的waits。因此，我們需要找出那些無用或低效索引的索引並刪除它們（找出無用索引可以透過索引監控的方法）。但是，直接刪除索引還是存在一定風險的。例如，某些索引可能只是在一些週期的作業中被使用到，而如果監控週期沒有覆蓋到這些作業的觸發點，就會認為索引是無用的而被刪除。當作業啟動後，可能就會對系統效能造成衝擊。這時，可能就會手忙腳亂的去找回索引定義語句、重建索引。11G之前，我們可以先不刪除索引，而將其修改為unusable。這樣的話，索引的定義並未刪除，只是索引不能再被使用也不會隨著表資料的更新而更新。當需要重新使用該索引時，需要用rebuild語句重建、然後更新統計資訊。對於一些大表來說，這個時間可能就非常長。在11g裡，Oracle 提供了一個新的特性來降低直接刪除索引或者禁用索引的風險，那就是索引不可見（Index Invisible）。

從版本11g 開始，可以建立不可見的索引。最佳化程式會忽略不可見的索引，除非在會話或系統級別上將 OPTIMIZER_USE_INVISIBLE_INDEXES初始化引數顯式設定為TRUE。此引數的預設值是FALSE。

使索引不可見是使索引不可用或刪除索引的一種替代辦法。使用不可見的索引，可完成以下操作：

(1) 在刪除索引之前測試對索引的刪除。

(2) 對應用程式的特定操作或模組使用臨時索引結構，這樣就不會影響整個應用程式。

注意：與不可用的索引不同，不可見的索引在使用DML 語句期間仍會得到維護。

當索引不可見時，最佳化程式生成的計劃不會使用該索引。如果未發現效能下降，則可以刪除該索引。還可以建立最初不可見的索引，執行測試，然後確定是否使該索引可見。

可以查詢*_INDEXES 資料字典檢視的VISIBILITY 列來確定該索引是VISIBLE 還是INVISIBLE。

SQL> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_ID';

VISIBILIT

---------

VISIBLE

--建立不可見索引：

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級別引數來使用不可見索引。

3.1 我的示例

建立表，不可見索引，並收集統計資訊：

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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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)

--這次又正常了。

以上是Oracle11g的處理方法，但是在Oracle9i或Oracle10g中索引沒有invisible的功能，我們如何處理呢？現在Oracle資料庫一般都採用基於成本的計算方法來生成執行計劃，只要索引的成本更低，ORACLE就會選擇使用索引，OK，那我們只要告訴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表示讓CACHE中的執行計劃立即失效，重新按現在的統計資訊生成SQL執行計劃。驗證一下是否生效：

SYS@lhrdb> set autot on9

SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 700947541

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

2491 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

3.2 OCP

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 index

B.making the index invisible

C.making the index unusable

D.using the MONITORING USAGE clause for the index

Answer:B

答案解析：題目要求在不能刪除和重建的情況下來測試索引的效能。

對於選項A，索引不能被禁用。所以，選項A錯誤。

對於選項B，讓索引不可用，為正確選項。所以，選項B正確。

對於選項C，讓索引不可用之後還是得重建索引。所以，選項C錯誤。

對於選項D，監控索引並不能測試索引在不可用的情況下對系統的效能影響。所以，選項D錯誤。

所以，本題的答案為B。

About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124044/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest/articles/5808049.html

● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號：230161599（滿）、618766405

● 微信群：可加我微信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友（646634621），註明新增緣由

● 於 2016-08-19 09:00~ 2016-08-19 19:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群小麥苗的微店

.............................................................................................................................................

Oracle之不可見索引（invisible indexes）

1 BLOG文件結構圖

2 前言部分

2.1 導讀和注意事項

2.2 相關參考文章連線

3 不可見索引(Invisible Indexes)

3.1 我的示例

3.2 OCP

相關文章