Oracle ANALYZE INDEX

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;',
'@id3.sql;'
from dba_indexes
where
owner not in ('SYS','SYSTEM');

spool off;

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

@id4.sql
@id5.sql