This post will try to outline the implications of rebuilding indexes. It will help customers to decide if index rebuild is required or not and if index coalesce is better.
There have been many discussions about whether rebuilding indexes is useful or not. Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.
The most common justifications given for rebuilding an index are:
- index becomes fragmented
- index grows and grows – deleted space is not re-used
- index clustering factor becomes out of sync
In fact, most indexes remain both balanced and fragmentation-free because free leaf entries will be reused. Inserts/Updates and Deletes result in free slots being scattered around the index blocks, but these will typically be refilled. The clustering factor reflects how sorted the table data is with respect to the given index key. Rebuilding an index never has an influence on the clustering factor but instead requires a table re-organization.
Secondly, the impact of rebuilding the index can be quite significant, please read the following comments thoroughly:
1. Most scripts around depend on the index_stats dynamic table. This is populated by the command:
analyze index ... validate structure;
While this is a valid method to inspect the index, it grabs an exclusive table lock while analyzing the index. Especially for large indexes, this can be very dramatic, as DML operations on the table are not permitted during that time. While it can be run online without the locking considerations, it may consume additional time.
2. Redo activity may increase and general performance might be impacted as a direct result of rebuilding an index.
Insert/update/delete causes the index to evolve over time as the index splits and grows. When the index is rebuilt, it will become more tightly packed; however as DML operations continue on the table, the index splits have to be redone again until the index reaches its equilibrium. As a result, the redo activity increases and the index splits are now more likely to impact performance directly as we consume more I/O, CPU, etc to serve the index restructuring. After a certain period of time, the index may again experience ‘issues’ and may be re-flagged for a rebuild, causing the vicious cycle to continue. Therefore, it is often better to leave the index in its natural equilibrium and/or at least prevent indexes from being rebuilt on a regular basis.
3. An index coalesce is often preferred instead of an index rebuild. It has the following advantages:
- does not require approximately 2 times the disk storage
- always online
- does not restructure the index, but combines index leaf blocks as much as possible, avoiding system overhead as explained in point 2.
Due to the reasons listed above, it is strongly advised not to rebuild indexes on a regular basis but instead use proper diagnostics.