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