Oracle 8 Tips   by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Features for the Data Warehouse

Determining when to rebuild 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.sql
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 Remote DBA_indexes
where
owner not in ('SYS','SYSTEM');

spool off;

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

@id4.sql
@id5.sql

Listing 8.4 The SQL to create a temporary table for the index_stats report.

Rem © 1997 by Donald K. Burleson
rem id2.sql
elect
name ,
most_repeated_key ,
distinct_keys ,
del_lf_rows ,
height ,
blks_gets_per_access
from index_stats;

Listing 8.5 The SQL to insert the data from index_stats into the temporary table

Rem © 1997 by Donald K. Burleson
rem id3.sql
nsert into temp_stats
(select
name ,
most_repeated_key ,
distinct_keys ,
del_lf_rows ,
height ,
blks_gets_per_access
from index_stats
);

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997. To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

|