PLSQL_性能優化系列13_Oracle Index Rebuild索引重建

2014-10-04 Created By BaoXinjian

一、摘要

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

1. 重建索引的理由

 Oracle的B樹索引随着時間的推移變得不平衡(誤解)

 索引碎片在不斷增加

 索引不斷增加，删除的空間沒有重複使用

 索引 clustering factor （叢集因子）不同步，可以通過重建修複(誤解)

2. 重建索引的本質

    本質：重建索引在資料庫内部是先執行删除操作，再執行插入操作。

3. 反對重建索引的理由

(1). 大多數腳本都依賴 index_stats 動态表。此表使用以下指令填充：

analyze index ... validate structure;

盡管這是一種有效的索引檢查方法，但是它在分析索引時會擷取獨占表鎖。對于大型索引，其影響會是巨大的，因為在此期間不允許對表執行DML 操作。

雖然該方法可以在不鎖表的情況下線上運作，但是可能要消耗額外的時間。

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

插入/更新/删除操作會導緻索引随着索引的分割和增長不斷發展。

重建索引後，它将連接配接的更為緊湊；但是，随着對表不斷執行 DML 操作，必須再次分割索引，直到索引達到平衡為止。

結果，重做活動增加，且索引分割更有可能對性能産生直接影響，因為我們需要将更多的 I/O、CPU 等用于索引重建。

經過一段時間後，索引可能會再次遇到“問題”，是以可能會再被标記為重建，進而陷入惡性循環。

是以，通常最好是讓索引處于自然平衡和（或）至少要防止定期重建索引。

4. Oracle的最終建議

    一般而言，極少需要重建 B 樹索引，基本原因是 B 樹索引很大程度上可以自我管理或自我平衡。

    大多數索引都能保持平衡和完整，因為空閑的葉條目可以重複使用。

    插入/更新和删除操作确實會導緻索引塊周圍的可用空間形成碎片，但是一般來說這些碎片都會被正确的重用。

    Clustering factor群集因子反映了給定的索引鍵值所對應的表中的資料排序情況。重建索引不會對群集因子産生影響，叢集因子隻能通過重組表的資料改變。

    強烈建議不要定期重建索引，而應使用合适的診斷工具。

    個人結論，如果重建索引的巨大工作量與之對應的是極小的收益，那就得不償失。如果系統有可用空閑期，重建之前和之後的測量結果表明性能有提高，值得重建。

5. 改良方法

    通常是優先考慮index coalesce（索引合并），而不是重建索引。索引合并有如下優點：

 不需要占用近磁盤存儲空間 2 倍的空間

 可以線上操作

 無需重建索引結構，而是盡快地合并索引葉塊，這樣可避免系統開銷過大。

6. 真正需要重建索引的情形

 索引或索引分區因媒體故障損壞

 标記為UNUSABEL的索引需要重建

 索引移動到新的表空間或需要改變某些存儲參數

 通過SQL*Loader加載資料到表分區後，需要重建索引分區

 重建索引以啟用鍵壓縮

 位圖索引本質不同于B樹索引，建議重建

二、案例

索引是提高資料庫查詢性能的有力武器。

沒有索引，就好比圖書館沒有圖書标簽一樣，找一本書自己想要的書比登天還難。

然而索引在使用的過程中，尤其是在批量的DML的情形下會産生相應的碎片，以及B樹高度會發生相應變化，是以可以對這些變化較大的索引進行重構以提高性能。

N久以前Oracle建議我們定期重建那些高度為4，已删除的索引條目至少占有現有索引條目總數的20%的這些表上的索引。

但Oracle現在強烈建議不要定期重建索引。

Thanks and Regards

參考：了沙彌 http://blog.csdn.net/leshami/article/details/23763963

參考：了沙彌 http://blog.csdn.net/leshami/article/details/24266247

<b>ERP技術讨論群: 288307890</b>

<b>技術交流，技術讨論，歡迎加入</b>

<b>Technology Blog Created By Oracle ERP - 鮑建立</b>