Gathering Statistics for Indexes

Optimizer statistics for indexes include things such as statistics relating to the number of rows, number of leaf blocks in the index, the number of levels of the B-tree, and the clustering factor of the index. Use the DBMS_STATS package to collect statistics for your indexes. As with tables, the frequency of statistics collection depends on the amount of changes your data is going through.

The DBMS_STATS Package

You can specify the collection of index statistics by specifying the CASCADE option with the GATHER_DATABASE_STATS, GATHER_SCHEMA_STATS, and GATHER_TABLE_STATS procedures, as shown in the following examples.

When collecting schema statistics:

SQL> execute dbms_stats.gather_schema_stats('HR', cascade=>TRUE); ...

Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.