【TUNE_ORACLE】索引定期重建的利與弊

索引必須定期重建嗎？

先來看看一些大部分人認為需要定期重建的理由：

1. 索引碎片會越來越多，而且這些碎片會被重複利用

2. B樹索引會隨著時間越來越不平衡

3. 索引聚簇因子隨著時間會變得不同步

4. 刪除索引後的空間不會被繼續利用

除了第一條正確，其他三條理由都不正確，因為絕大多數情況下，是不需要重建B樹索引的，B樹索引一般能夠自動管理和自動平衡。下面來分析每條的實際情況及原因：

第一條：DML操作會導致索引塊附近的可用空間形成碎片，但這些碎片會被重複使用，建議不要在有索引的列上進行頻繁DML操作，以減少碎片

第二條：B樹索引會自動平衡

第三條：聚簇因子只能透過重組表中資料來改變

第四條：和第一條一樣，索引空間依然能被重複利用

 

什麼時候需要考慮重建索引？

1. 索引深度最好不要超過3級，否則重建。但是如果索引超過了3級但是深度不再發生變化，就不需要重建

2. 查詢INDEX_STATS的DEF_LF_ROWS與LF_ROWS的比值如果大於0.2（表示刪除的索引條目佔索引總條目的20%），或者BLKS_GETS_PER_ACCESS大於10，就需要重建索引

具體操作如下：

SQL> ANALZE INDEX XXX VALIDATE STRUCTURE;   --必須執行索引分析這一步，不然查不出結果

SQL> select HEIGHT, DEF_LF_ROWS/ LF_ROWS , BLKS_GETS_PER_ACCESS from INDEX_STATS;

注：“ANALZE INDEX XXX VALIDATE STRUCTURE”對於分析空間佔用大的索引時會對業務產生巨大影響，因為在執行期間不允許DML操作，並且需要消耗額外時間（不會鎖表）

 

可能產生的影響

重建索引會導致redo工作量進一步增加，導致真個DB效能產生一定影響。雖然重建索引會使得索引塊更加集中，但是會隨著DML操作的增多導致碎片增多，因此索引在每次DML後，為了達到索引平衡，會重新分割索引來調整索引結構，需要佔用I/O和CPU資源，並且經過一段時間後，該索引會繼續被標記成“REBUILD”，需要繼續重建。所以，強烈建議使用索引預設的自平衡機制，而不是定期重建索引。

一般使用索引合併來取代索引重建，理由如下：

1. 佔用空間小

2. 只會合併索引塊，而不是去重建索引導致索引結構改變

 

總結

強烈不建議定期重建索引，並且不建議對含有索引的列進行頻繁DML操作！！