| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Determining when to rebuild indexes
del_lf_rows-- This columns refers to the number of leaf rows
that have been deleted from the index. This occurs when heavy index
update activity occurs within the index tree, and indicated that the
index will benefit from being dropped and re-created.
distinct_keys-- This indicates the number of distinct key
values in the index. This is called the cardinality of the index,
and values less than 20 are candidates for being re-created as
bitmapped indexes.
most_repeated_key-- This column counts the number of times
that the most frequent key value in a non-unique index appears in
the b-tree.
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 analyze index xxx validate structure for
each index in the schema and report of the resulting values in
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 Remote DBA suspects
that update activity may have unbalanced the indexes.
|
