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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
