| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Understanding Oracle indexes
* BLEVEL--This is the number of levels that the index has
spawned. Even for very large indexes, there should never be more
than four levels. Each BLEVEL represents an additional I/O that must
be performed against the index tree.
* LEAF_BLOCKS--This is a reference to the total number of
leaf blocks.
* DISTINCT_KEYS--This is a reference to the cardinality of
the index. If this value is less than 10, you may want to consider
redefining the index as a bitmapped index.
* AVG_DATA_BLOCKS_PER_KEY--This is a measure of the size of
the index and the cardinality of the index. A low cardinality index
(i.e., sex or region) will have high values, as will very large
indexes.
* CLUSTERING_FACTOR--This is the most important measure in
this report, since it measures how balanced the index is, relative
to the table. If the clustering factor is near the number of blocks
in the table, then the table is said to be clustered within the
index. This is good for data retrieval with the index, since there
will be less physical I/O. If the clustering factor approaches the
number of rows in the table, then the index is said to be random.
That is, the index keys are not in the same physical order as the
rows in the table. Of course, only one index on a table will have a
high clustering factor, since the rows can only be physically
ordered to match one index key value.
* AVG_LEAF_BLOCKS_PER_KEY--This is always one, with the
exception of non unique indexes.
These statistics give Oracle’s cost-based optimizer clues about
which indexes are best suited to servicing each query.
|
