Database - What are Index statistics?

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
b. Levels
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:

Package	Description
GATHER_INDEX_STATS	Index statistics
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