Database Reference
In-Depth Information
clustering_factor
indicates how many adjacent index entries don't refer to the same data
block in the table. If the table and the index are sorted similarly, the clustering factor is low.
The minimum value is the number of nonempty data blocks in the table. If the table and the
index are sorted differently, the clustering factor is high. The maximum value is the number
of keys in the index. I discuss the way it's computed and its performance impact in detail
in Chapter 13. It's worth mentioning that for bitmap indexes, no real clustering factor is
computed. Actually, it's set to the number of keys in the index.
•
•
avg_leaf_blocks_per_key
is the average number of leaf blocks that store a single key. This
value is derived from other statistics using Formula 8-2.
Formula 8-2.
Computation of the average number of leaf blocks that store a single key
leaf_blocks
distinct_keys
avg_leaf_blocks_per_key
»
avg_data_blocks_per_key
is the average number of data blocks in the table referenced by a
single key. This value is derived from other statistics using Formula 8-3.
•
Formula 8-3.
Computation of the average number of data blocks referenced by a single key
clustering_factor
distinct_keys
avg_data_blocks_per_key
»
Statistics for Partitioned Objects
Partitioned objects are logical constructs composed of sets of segments. For example, the following SQL statement
creates a partitioned table with 16 segments, as shown in Figure
8-6
. While the 16 segments are objects that actually
hold data in a tablespace, the four partitions and the table are metadata-only objects. They only exist in the data
dictionary:
CREATE TABLE t (id NUMBER, tstamp DATE, pad VARCHAR2(1000))
PARTITION BY RANGE (tstamp)
SUBPARTITION BY HASH (id)
SUBPARTITION TEMPLATE
(
SUBPARTITION sp1,
SUBPARTITION sp2,
SUBPARTITION sp3,
SUBPARTITION sp4
)
(
PARTITION q1 VALUES LESS THAN (to_date('2014-04-01','YYYY-MM-DD')),
PARTITION q2 VALUES LESS THAN (to_date('2014-07-01','YYYY-MM-DD')),
PARTITION q3 VALUES LESS THAN (to_date('2014-10-01','YYYY-MM-DD')),
PARTITION q4 VALUES LESS THAN (to_date('2015-01-01','YYYY-MM-DD'))
)
