Why or When rebuild the Index and Impact of rebuilding????

Why or When rebuild the Index:

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 for a very large amount self-managed or self-balanced. Commonly the following reasons 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 results in free slots being scattered around the index blocks, but these will typically be refilled add even rates. 
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.

Rebuild the index when these conditions are true:

1.deleted entries represent 20% or more of the current entries.
select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats 
where name = ‘index_ name’;

The index stats table can only hold one record of information at a time, therefore you will need to analyze each index individually and then interrogate index_stats, you can also automate this process using pl/sql.

2. the index depth is more than 4 levels.
select * from dba_indexes where blevel>3;

Impact Of  Rebuilding the Index:
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.

2. Redo activity and general performance may increase 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 rebuild 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 natural 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 flagged for a rebuild again causing the vicious cycle to continue. Therefor it is often better to leave the index in its natural equilibrium and/or at least prevent indexes from being rebuild on a regular basis.

3. An index coalesce is often preferred over an index rebuild. It has the following advantages:
– does not require approximately 2 times the disk storage
– always online
– no locking issues
– does not restructure the index, but combines index leaf blocks as much as possible, avoiding system overhead as explained in point 2.
Note:  To re-allocate an index, to another tablespace for example a rebuild is required.

Due to the above it is strongly advised not to rebuild indexes on a regular basis but instead use proper diagnostics. Especially avoid to rebuild index on a regular basis as there is a reason why the index becomes in this state over-and-over again. 

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply