What are Index statistics?
- Optimizer statistics are a collection of data that describe more details about the database and the objects in the database.
- These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:
1. Table statistics
a. Number of rows
b. Number of blocks
c. Average row length
2. Column statistics
a. Number of distinct values (NDV) in column
b. Number of nulls in column
c. Data distribution (histogram)
3. Index statistics
a. Number of leaf blocks
c. Clustering factor
4. System statistics
a. I/O performance and utilization
b. CPU performance and utilization
- Statistics are gathered using the DBMS_STATS package. This PL/SQL package is also used to modify, view, export, import, and delete statistics.
- Statistics gathering procedures in the DBMS_STATS package are as follows:
|GATHER_TABLE_STATS||Table, column, and index statistics|
|GATHER_SCHEMA_STATS||Statistics for all objects in a schema|
|GATHER_DICTIONARY_STATS||Statistics for all dictionary objects|
|GATHER_DATABASE_STATS||Statistics for all objects in a database|