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.
index DON.DON_FK_PLT validate structure;
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.
set heading off;
set feedback off;
set echo off;
'@id2.sql' from dual;
'analyze index '||owner||'.'||index_name||' validate structure;',
owner not in
set feedback on;
set echo on;
Listing 8.6 This SQL is generated from
Rem © 1997 by Donald K. Burleson