When to rebuild index ?

Index is one of interesting objects in database, which always attract DBAs and Developers to fine tune their queries. Because index is just like salt in food. It should be used optimized quantity. Some time index doesn't show actual expected performance. In those cases we need to check whether index need to be rebuild or not.

There are certain cafeterias for rebuilding an index:

The number of deleted leaf nodes :-
The number of index nodes that have been logically deleted as a result of  row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to re balance the index tree when rows are deleted.

Index height :-  The number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels.

Gets per index access :- The amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands:

ANALYZE INDEX index_name COMPUTE STATISTICS;
ANALYZE INDEX index_name VALIDATE STRUCTURE;

After you analyze the report above, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries.

As you may know, you can easily rebuild an Oracle index with the command:

ALTER INDEX index_name REBUILD;



      ********************