【DB筆試面試352】什麼是不可用索引（Unusable Indexes），哪些操作會導致索引變為不可用（unusable）即失效狀態？

---

---

（一）什麼是不可用索引（Unusable Indexes）？對於不可用索引需要注意哪些？

在正常情況下，索引都是可用的。對於可用的索引而言，DBA_INDEXES的STATUS列的值為VALID。當執行某些操作後，例如對索引執行MOVE操作，索引會變為不可用（unusable），即處於失效狀態。當索引變為不可用時，Oracle內部會把該索引後設資料（即建立語句包含的基本資訊)與真實物理資料之間的對應關係撕裂，相關資料塊可被重用，相關的索引段會被刪除，DBA_INDEXES的SEGMENT_CREATED列的值會變為NO（正常情況下，應為YES），STATUS列的值變為UNUSABLE。對於失效的索引，若想要被重新使用，則只有一種方法，那就是REBUILD，當然也可以先DROP 再CREATE。

對於失效的索引需要注意以下幾點：

l 從Oracle 11gR2開始，當索引不可用時，Oracle會清除索引資料（刪除相關的索引段），並且不再維護。

l 在本地索引中，可以設定某個分割槽的索引為UNUSABLE。

l 一般情況下，CBO不考慮使用UNUSABLE狀態的索引，包含分割槽表。相關引數為SKIP_UNUSABLE_INDEXES，其作用如下所示：

引數作用	控制對UNUSABLE 的非唯一索引，DML操作時是否維護索引資料。
引數值	true | false
	當引數設定為true時（預設），當資料庫遇到不可用索引時，只會忽略而不會提示任何錯誤資訊；同時即使該表上帶有不可用的索引或索引分割槽，也可以針對該表執行DML操作，針對不可用索引對應的DML語句都將正常執行，而且資料庫停止維護相關索引。 在進行DML（INSERT和UPDATE）操作時，如果是唯一索引，那麼無論該引數的值設定為何值，Oracle都會去校驗索引的可用性。如果索引不可用，那麼就會報錯ORA-01502。
	當引數設定為false時，DML操作及CBO生成執行計劃時都會考慮該索引。所有與UNUSABLE索引相關的操作都會失敗，報錯資訊為：ORA-01502: 索引 'SCOTT.INDEX_T2' 或這類索引的分割槽處於不可用狀態。

l 在重建索引的時候一般不允許DML操作，只有加上ONLINE的時候才允許DML操作。

 

 

（二）哪些操作會導致索引失效？

 

當某些操作導致資料行的ROWID改變，索引就會完全失效。可以分普通表和分割槽表來討論哪些操作將導致索引失效。

（1）普通表索引失效的情形如下所示：

①　手動置索引無效：ALTER INDEX IND_OBJECT_ID UNUSABLE;。

②　如果對錶進行MOVE操作（包含移動表空間和壓縮操作）或線上重定義表後，那麼該表上所有的索引狀態會變為UNUSABLE。MOVE操作的SQL語句為：ALTER TABLE TT MOVE;。

③　SQL*Loader載入資料。

在SQL*Loader載入過程中會維護索引，由於資料量比較大，在SQL*Loader載入過程中出現異常情況，也會導致Oracle來不及維護索引，導致索引處於失效狀態，影響查詢和載入。異常情況主要有：在載入過程中殺掉SQL*Loader程式、重啟或表空間不足等。

（2）分割槽表索引失效的情形如下所示：

①　對分割槽表的某個含有資料的分割槽執行了TRUNCATE、DROP操作可以導致該分割槽表的全域性索引失效，而分割槽索引依然有效，如果操作的分割槽沒有資料，那麼不會影響索引的狀態。需要注意的是，對分割槽表的ADD操作對分割槽索引和全域性索引沒有影響。

②　執行EXCHANGE操作後，全域性索引和分割槽索引都無條件地會被置為UNUSABLE（無論分割槽是否含有資料）。但是，若包含INCLUDING INDEXES子句（預設情況下為EXCLUDING INDEXES），則全域性索引會失效，而分割槽索引依然有效。

③　如果執行SPLIT的目標分割槽含有資料，那麼在執行SPLIT操作後，全域性索引和分割槽索引都會被被置為UNUSABLE。如果執行SPLIT的目標分割槽沒有資料，那麼不會影響索引的狀態。

④　對分割槽表執行MOVE操作後，全域性索引和分割槽索引都會被置於無效狀態。

⑤　手動置其無效：ALTER INDEX IND_OBJECT_ID UNUSABLE;。

對於分割槽表而言，除了ADD操作之外，TRUNCATE、DROP、EXCHANGE和SPLIT操作均會導致全域性索引失效，但是可以加上UPDATE GLOBAL INDEXES子句讓全域性索引不失效。

重建分割槽索引的命令為：ALTER INDEX IDX_RANG_LHR REBUILD PARTITION P1;。

分割槽表的索引是否失效情況如下表所示：

	目標分割槽有資料					目標分割槽無資料
操作動作	操作命令	全域性索引		分割槽索引
		是否失效	如何避免失效	是否失效	如何避免失效
TRUNCATE分割槽	ALTER TABLE PT_TRUNC_LHR TRUNCATE PARTITION P1;	失效	操作語句後加UPDATE GLOBAL INDEXES	沒影響		對全域性索引和分割槽索引都沒有影響
DROP分割槽	ALTER TABLE PT_DROP_LHR DROP PARTITION P1;			沒影響
SPLIT分割槽	ALTER TABLE PT_SPLIT_LHR SPLIT PARTITION P_MAX  AT (30000) INTO (PARTITION P3,PARTITION P_MAX);			如果MAX區中已經有記錄了，這個時候SPLIT就會導致有記錄的新增分割槽的區域性索引失效!	對區域性索引進行重建索引ALTER INDEX IDX_PART_SPLIT_COL3 REBUILD;
EXCHANGE分割槽	ALTER TABLE PT_EX_LHR EXCHANGE PARTITION P1 WITH TABLE T_NM_LHR INCLUDING INDEXES;			沒影響		和有資料時一致
	ALTER TABLE PT_EX_LHR EXCHANGE PARTITION P1 WITH TABLE T_NM_LHR;			失效	對區域性索引進行重建索引ALTER INDEX IDX_PART_EX_COL3 REBUILD;
ADD分割槽	ALTER TABLE PT_ADD_LHR ADD PARTITION P6 VALUES LESS THAN (6666);	對全域性索引和分割槽索引都沒有影響

 

& 說明：

有關資料庫操作導致索引失效的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152871/

Oracle之不可見索引 ：http://blog.itpub.net/26736162/viewspace-2124044/

Oracle之虛擬索引 ：  http://blog.itpub.net/26736162/viewspace-2123687/

Oracle之不可見索引和虛擬索引的比對：http://blog.itpub.net/26736162/viewspace-2124164/

 

---

---

DB筆試面試歷史連線 ：http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w

---

---

Oracle之不可見索引 ：http://blog.itpub.net/26736162/viewspace-2124044/

Oracle之虛擬索引 ：  http://blog.itpub.net/26736162/viewspace-2123687/

 

---

---

Beginning with Oracle Database 11g Release 2, when you make an existing index unusable, its index segment is dropped.

---

---

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（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/ ● DBA寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成 ● 最新修改時間：2018-05-21 06:00 ~ 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面試寶典》讀者群       小麥苗的微店 .............................................................................................................................................