Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle Database Tips by Donald Burleson

How can you tell when an index would benefit from being rebuilt?  There are two Oracle views that provide index statistics, DBA_INDEXES and INDEX_STATS.  The DBA_INDEX view contains statistical information that is placed into the view when the Oracle ANALYZE INDEX xxx command is issued.  Unfortunately, the DBA_INDEXES view does not keep statistics about the internal status of the Oracle indexes, as it was was designed to provide information to the cost-based SQL optimizer.  The Oracle ANALYZE INDEX xxx VALIDATE STRUCTURE  SQL command can be used to validate the structure for the index.  This command creates a single row in a view called INDEX_STATS.

SQL> analyze index DON.DON_FK_PLT validate structure;

Index analyzed.

The Oracle index_stats view will never contain more than one row.  Therefore, you must perform the Oracle "analyze index xxx validate structure" command and "select * from index_stats" before issuing the next Oracle analyze index command.  The script id1.sql provides a method for getting a complete report for all indexes.

Since the index_stats view will only hold one row at a time, it is not easy to create a SQL*Plus routine that will produce a index_stats report for all of the indexes on a system.  The SQL in listing 8.3 will perform an Oracle analyze index xxx validate structure for each index in the schema and report of the resulting values in the Oracle index_stats. 

Note:  Running id1.sql will invoke id2.sql through id5.sql automatically producing the unbalanced index report.  Just be sure that id1.sql through id5.sql are present in a common directory when starting id1.sql.

Despite the complexity of dealing with a one-row index_stats table, it is easy to use the following script to get index_stats for all warehouse indexes. (listings 8-3 through 8-8) In operational use, the unbalanced index report would be run whenever the DBA suspects that update activity may have unbalanced the indexes.

listing 8.3 The SQL*Plus script to generate the report for index_stats.
rem   id1.sql   The main driver routine for reporting index_stats
Rem © 1997 by Donald K. Burleson

REM id1.sq
set pages 9999;
set heading off;
set feedback off;
set echo off;

spool id4.sql;

select '@id2.sql' from dual;

select 'analyze index '||owner||'.'||index_name||' validate structure;',
from dba_indexes
owner not in ('SYS','SYSTEM');

spool off;

set heading on;
set feedback on;
set echo on;


Listing 8.6  This SQL is generated from running id1.sql

Rem © 1997 by Donald K. Burleson

rem id4.sql

analyze index DON.SHL_EK_TRUCK_LINK_NUM validate structure;


analyze index DON.SHL_UK_FACT1_ID_SRC_CD_LOB validate structure;


analyze index DON.PURCH_UNIT_PK validate structure;


Related ANALYZE INDEX articles:




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.