| |
 |
|
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.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;
@id3.sql;
analyze index DON.SHL_UK_FACT1_ID_SRC_CD_LOB validate structure;
@id3.sql;
analyze index DON.PURCH_UNIT_PK validate structure;
@id3.sql;
Listing 8.7 This SQL*Plus script generates the clustering report
Rem © 1997 by Donald K. Burleson
rem id5.sql - This creates the unbalanced index report and the
rebuild syntax
set pagesize 60;
set linesize 100;
set echo off;
set feedback off;
set heading off;
column c1 format a18;
column c2 format 9,999,999;
column c3 format 9,999,999;
column c4 format 999,999;
column c5 format 99,999;
column c6 format 9,999;
spool idx_report.lst;
prompt
prompt
prompt ' # rep dist. # deleted blk s
prompt Index keys keys leaf rows Height per s
prompt -------------------- ------ ----- -------- ------ -----
select distinct
name c1,
most_repeated_key c2,
distinct_keys c3,
del_lf_Rows c4,
height c5,
blks_gets_per_access c6
from temp_stats
where
height > 3
or
del_lf_rows > 10
order by name;
spool off;
spool id6.sql;
|
