Database Reference
In-Depth Information
Figure 8-5.
The structure of an index is based on a B
+
-tree
Not all indexes have the three types of blocks. In fact, the branch blocks exist only if the root block isn't able to
store the references of all the leaf blocks. In addition, if the index is very small, it consists of a single block containing
all the data usually stored in the root block and the leaf blocks.
The following query shows how to get the most important index statistics for a table:
SQL> SELECT index_name AS name,
2 blevel,
3 leaf_blocks AS leaf_blks,
4 distinct_keys AS dst_keys,
5 num_rows,
6 clustering_factor AS clust_fact,
7 avg_leaf_blocks_per_key AS leaf_per_key,
8 avg_data_blocks_per_key AS data_per_key
9 FROM user_ind_statistics
10 WHERE table_name = 'T';
NAME BLEVEL LEAF_BLKS DST_KEYS NUM_ROWS CLUST_FACT LEAF_PER_KEY DATA_PER_KEY
-------- ------ --------- -------- -------- ---------- ------------ ------------
T_PK 1 2 1000 1000 979 1 1
T_VAL1_I 1 2 431 497 478 1 1
T_VAL2_I 1 3 6 1000 175 1 29
The index statistics returned by this query are as follows:
blevel
is the number of branch blocks to be read, including the root block, in order to access
a leaf block.
•
leaf_blocks
is the number of leaf blocks of the index.
•
distinct_keys
is the number of distinct keys in the index.
•
num_rows
is the number of keys in the index. For primary keys, this is the same as
distinct_keys
.
•
