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:

GATHER_INDEX_STATSIndex statistics
GATHER_TABLE_STATSTable, column, and index statistics
GATHER_SCHEMA_STATSStatistics for all objects in a schema
GATHER_DICTIONARY_STATSStatistics for all dictionary objects
GATHER_DATABASE_STATSStatistics for all objects in a database
Database - Describe Fragmentation
Database fragmentation....
Database - Explain Nested Join, Hash Join, and Merge Join in SQL Query Plan
Nested Join, Hash Join, and Merge Join in SQL Query Plan.....
What are Notification services?
Notification Services are services which send notifications to the interested entities....
Post your comment