regarding index analyze.

All posts relating to Oracle database administration.

Moderator: Tim...

Posts: 260
Joined: Sat Jun 27, 2009 3:53 am
Location: India

regarding index analyze.

Postby sivakumarocp » Wed Apr 04, 2012 5:22 am

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.

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: regarding index analyze.

Postby Tim... » Wed Apr 04, 2012 10:07 am


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:


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 3 guests