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.