Analyzing the extent of index fragmentation and rebuilding the indexes for performance improvement

Hi Readers, Glad to meet you again. Carrying forward the promise made in my earlier post I am writing here how to analyze the extent of fragmentation an index is having and how to rebuild an index in case that is required.

Click here to know more

1. Analyzing the extent of index fragmentation : There are various factors which help us to analyze this and below are the main ones.

a)      The number of deleted leaf nodes - The number of index nodes that have been logically deleted as a result of row deletes.

b)      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 initiate additional levels of an index to accommodate the new rows.

The commands to analyze an index are below.

ANALYZE INDEX index_name COMPUTE STATISTICS 

ANALYZE INDEX index_name VALIDATE STRUCTURE 

After you analyze the reports above, you may consider rebuilding the index where the height is more than three levels. I will explain other details in next post. Stay Tuned!!! Stay hungry to learn :)