Databases Reference
In-Depth Information
the results of the
analyze
command. It further recommends you to rebuild or coalesce an index "when
you find that index space usage drops below its average." These recommendations are definitely at odds
with those currently offered on the Oracle support site (MOSC). On that site, Oracle no longer advises
frequent index rebuilds, notwithstanding the latest version of Oracle's documentation (
Performance
Tuning Manual for Oracle Database 11.2
) which still contains the older advice to run the
analyze ...
validate
statement to identify index rebuild candidates.
The following sections examine in detail the
INDEX_STATS
view and the
analyze index…validate
structure
command that are at the heart of the whole rebuilding strategy that Oracle still half-heartedly
recommends.
The Role of the INDEX_STATS View in Index Rebuilds
The
INDEX_STATS
view by default has no rows. You populate this view by executing the
analyze index...
validate structure
command. Once you do this, the
INDEX_STATS
will supposedly have the necessary
data to guide your index rebuild decisions.
Benefits from the INDEX_STATs view
Once you have the view populated, you can use it to look at and compute a number of useful items of
information that can help you stay on top of indexing in your database. The key columns you need to
pay attention to are the following:
•
HEIGHT: Height of the index, which begins at 1 for root only index.
•
BLOCKS: Number of blocks allocated to the index.
•
LF_ROWS: Number of leaf row entries (includes deleted row entries).
•
DEL_LF_ROWS: Number of deleted leaf row entries not yet cleaned out.
•
USED_SPACE: Total space used within the index (includes deleted entries).
•
PCT_USED: Percentage of space used within the index (includes deleted entries).
This is derived by the following formula: (USED_SPACE/BTREE_SPACE)*100.
•
BTREE_SPACE: Total size of the index (includes deleted entries).
You can estimate the non-deleted rows in an index by subtracting the DEL_LF_ROWS value from
the LF_ROWS value. You can estimate the percentage of space used by the non-deleted rows of an
indexed by using the following formula:
((USED_SPACE - DEL_LF_ROWS_LEN)/BTREE_SPACE) * 100
