How to Determine When an Index Should be Rebuilt?

Hello,
The reason to rebuild an index should be because of poor performance of your queries using the index. In this blog we will discuss 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.
Most scripts around depend on the index_stats dynamic table. This is populated by the command:

analyze index index_owner.index_name 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.

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. 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. 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.

To rebuild indexes;

1- Find indexes having height(blevel+1) > 4 . Indexes having BLEVEL > 3