regarding index analyze.

[sivakumarocp]

Hi Tim,

How do we identify index fragmentation.
Usually we can achive this by doing ANALYZE INDEX <Index_name> VALIDATE STRUCTURE;
if we do so informaiton about that index has been populated on Index_stats table, from this we can see the deleted entries percentage. usually if particular index deleted entries has more than 20% we can say this index got fragmented and its candidate for rebuild!

Please tell me upto this my understanding on Index Fragmentation is correct or not.

In production environment can we do ANALYZE INDEX <Index_name> VALIDATE STRUCTURE; if we do so does it make lock on this index?

Please post any scripts to find index fragmentation.

[Tim...]

Hi.

For the most part you can leave indexes alone. Oracle manages them pretty well. Lots of people have tried to write a script or process to predict when an index needs rebuilding, but the truth is it is not predictable. An index needs rebuilding if changes internally affect its usage by the optimizer. That is almost impossible to predict.

Read Tom Kyte's post on this subject: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3035290021641

Cheers

Tim...