Lesson 6	Computing Statistics
Objective	Describe two methods of creating statistics in Oracle

Computing Statistics in Oracle

Oracle’s optimizer relies on object statistics (table and index statistics, column cardinality, histograms, and related metadata) to evaluate execution plans and choose an efficient access path. If statistics are stale or missing, the optimizer can misestimate row counts and costs, leading to inefficient plans (for example, the wrong join order, a poor join method, or a full scan when an index access path would be better).
In Oracle 23ai, the core principle remains the same: good statistics enable good plans. The difference today is that Oracle environments typically rely on automatic statistics gathering and the DBMS_STATS package as the standard approach, while the older ANALYZE ... COMPUTE STATISTICS syntax is mainly retained for legacy compatibility.

This lesson specifically examines index statistics and how they become available to the optimizer.

Two Methods of Creating Index Statistics

This lesson focuses on two common ways index statistics become available to the optimizer:

Compute statistics during index creation or rebuild (legacy SQL syntax).
Gather statistics explicitly after the fact (modern best practice via DBMS_STATS).

Method 1: Compute Statistics During Index Creation

Oracle supports computing index statistics as part of index creation. This is syntactically valid and can be convenient in legacy scripts. However, most modern environments prefer DBMS_STATS for consistency and richer control.

CREATE INDEX index_name
ON table_name (column_list)
COMPUTE STATISTICS;

Creates the index and computes statistics as part of the build. This is valid syntax, but in Oracle 23ai you will typically rely on automatic statistics gathering or DBMS_STATS for controlled, repeatable stats management.

CREATE INDEX	Required keywords.
index_name	Unique name for the index.
ON	Required keyword.
table_name	Name of the table the index will be based on.
column_list	List of columns that make up the index key.
COMPUTE STATISTICS	Computes index statistics during index creation (legacy approach).

Method 1 Variation: Gather Statistics During an Index Rebuild

You can also compute statistics while rebuilding an index. This can save a separate stats-gathering step in older workflows:

ALTER INDEX hr.emp_emp_id_pk REBUILD COMPUTE STATISTICS;

In modern practice, you would typically rebuild only when you have a clear reason, and then gather statistics using DBMS_STATS (or allow automatic jobs to handle it), depending on your operational standards.

Method 2: Gather Statistics Explicitly After Index Creation

The recommended, modern method is to use DBMS_STATS. It provides better control (sampling, histograms, partition-level stats, and consistent behavior across tables and indexes) and aligns with Oracle’s standard statistics management model.

-- Gather index statistics explicitly (preferred modern approach)
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => USER,
    indname => 'INDEX_NAME'
  );
END;
/

Legacy Explicit Method: ANALYZE INDEX ... COMPUTE STATISTICS

For completeness (and because you will still encounter it in older scripts), Oracle also supports computing index statistics with the ANALYZE statement. The key point is that the correct syntax for indexes is:

There is no column “target list” for ANALYZE INDEX. The statement applies to the index as an object.

ANALYZE INDEX index_name COMPUTE STATISTICS;

Valid legacy syntax to compute statistics for a specific index. In Oracle 23ai, prefer DBMS_STATS unless you have a strict legacy requirement to keep ANALYZE.

When Should You Refresh Statistics?

Statistics are most valuable when they reflect the current “shape” of your data. Minor changes often do not change optimizer decisions, but major shifts can. As a general rule, refresh statistics when:

You have performed a large data load, purge, or archival operation.
Data distribution changes materially (for example, new values or large changes in cardinality).
You have changed physical design (rebuilds, partition maintenance, table moves) and need stable costing.

Most Oracle deployments rely on automatic statistics gathering during maintenance windows for routine upkeep, and use explicit gathering for targeted, high-impact changes.

Computing Statistics - Quiz

Click the Quiz link below to test your knowledge about index enhancements.
Computing Statistics - Quiz