【DB筆試面試352】什麼是不可用索引（Unusable Indexes），哪些操作會導致索引變為不可用即失效狀態？
- 2019 年 10 月 10 日
- 筆記
Q
題目
在Oracle資料庫中，什麼是不可用索引（Unusable Indexes），哪些操作會導致索引變為不可用（unusable）即失效狀態？
A
答案
（一）什麼是不可用索引（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/
