Oracle 重建索引的必要性

      索引重建是一個爭論不休被不斷熱烈討論的議題。當然Oracle官方也有自己的觀點，我們很多DBA也是遵循這一準則來重建索引，那就是Oracle建議對於索引深度超過4級以及已刪除的索引條目至少佔有現有索引條目總數的20% 這2種情形下需要重建索引。近來Oracle也提出了一些與之相反的觀點，就是強烈建議不要定期重建索引。本文是參考了1525787.1並進行相應描述。

 

1、重建索引的理由
    a、Oracle的B樹索引隨著時間的推移變得不平衡(誤解)
    b、索引片段在不斷增加
    c、索引不斷增加，刪除的空間沒有重複使用
    d、索引 clustering factor （叢集因子）不同步，可以通過重建修複(誤解)

 

2、重建索引的本質
    本質：重建索引在資料庫內部是先執行刪除操作，再執行插入操作。

 

3、反對重建索引的理由
    a. 大多數指令碼都依賴 index_stats 動態表。此表使用以下命令填充：
        analyze index ... validate structure;

       儘管這是一種有效索引檢查方法，但是它在分析索引時會擷取獨佔表鎖。對於大型索引，其影響會是巨大的，因為在此期間不允許對錶執行DML 操作。
       雖然該方法可以在不鎖表的情況下線上運行，但是可能要消耗額外的時間。

    b. 重建索引的直接結果是 REDO 活動可能會增加，總體系統負載也可能會提高。

       插入/更新/刪除操作會導致索引隨著索引的分割和增長不斷髮展。
       重建索引後，它將串連的更為緊湊；但是，隨著對錶不斷執行 DML 操作，必須再次分割索引，直到索引達到平衡為止。
       結果，重做活動增加，且索引分割更有可能對效能產生直接影響，因為我們需要將更多的 I/O、CPU 等用於索引重建。
       經過一段時間後，索引可能會再次遇到“問題”，因此可能會再被標記為重建，從而陷入惡性迴圈。
       因此，通常最好是讓索引處於自然平衡和（或）至少要防止定期重建索引。

 

4、Oracle的最終建議
    一般而言，極少需要重建 B 樹索引，基本原因是 B 樹索引很大程度上可以自我管理或自我平衡。
    大多數索引都能保持平衡和完整，因為空白閑的葉條目可以重複使用。
    插入/更新和刪除操作確實會導致索引塊周圍的可用空間形成片段，但是一般來說這些片段都會被正確的重用。
    Clustering factor群集因子反映了給定的索引索引值所對應的表中的資料排序情況。重建索引不會對群集因子產生影響，叢集因子只能通過重組表的資料改變。
    強烈建議不要定期重建索引，而應使用合適的診斷工具。
    個人結論，如果重建索引的巨大工作量與之對應的是極小的收益，那就得不償失。如果系統有可用空閑期，重建之前和之後的測量結果表明效能有提高，值得重建。
 
5、改良方法
    通常是優先考慮index coalesce（索引合并），而不是重建索引。索引合并有如下優點：
       a、不需要佔用近磁碟儲存空間 2 倍的空間
       b、可以線上操作
       c、無需重建索引結構，而是儘快地合并索引葉塊，這樣可避免系統開銷過大。

 

6、真正需要重建索引的情形
    索引或索引分割區因介質故障損壞
    標記為UNUSABEL的索引需要重建
    索引移動到新的資料表空間或需要改變某些儲存參數
    通過SQL*Loader載入資料到表分區後，需要重建索引分割區
    重建索引以啟用鍵壓縮
    位元影像索引本質不同於B樹索引，建議重建

 

7、相關參考
    Oracle 聚簇因子(Clustering factor) 
    Oracle 索引監控(monitor index)
    Oracle 索引監控與外鍵索引 
    收集統計資訊導致索引被監控 
    Oracle 監控索引的使用率
    NULL 值與索引(一)
    NULL 值與索引(二)
    函數使得索引列失效

    Oracle 索引品質分析