How to Run the ANALYZE Command

How you run the ANALYZE command is determined by the type of statistics or analysis you want to perform. The ANALYZE command can be used in several different modes. The mode you choose depends on the data you want to gather as well as on the configuration of your system.

Using ANALYZE to Gather Statistics

You can use the ANALYZE command to gather statistics in one of two modes. The first mode scans the entire table, cluster, or index and calculates statistics exactly, based on your data. Although this is the most accurate method, it requires enough temporary space to hold and sort all the rows of the table or cluster (no space is required for an index). Computing the statistics also uses a great deal of system resources.

The second mode of the ANALYZE command estimates statistics. This method performs a sampling of the table, cluster, or index in order to estimate statistics. In this method, the entire table or cluster is not scanned; a portion of the data is used to determine the statistics. The amount of data used can be specified when you invoke the ANALYZE command.

Using ANALYZE to Compute Exact Statistics

To use the ANALYZE command to compute exact statistics, invoke ANALYZE with one of the following syntaxes.

For Tables:

ANALYZE TABLE table_name

    COMPUTE STATISTICS;

For Table Partitions:

ANALYZE TABLE table_name PARTITION (partition_name)

    COMPUTE STATISTICS;

For Clusters:

ANALYZE CLUSTER cluster_name

    COMPUTE STATISTICS;

For Indexes:

ANALYZE INDEX index_name

    COMPUTE STATISTICS;

For Index Partitions:

ANALYZE INDEX index_name PARTITION (partition_name)

    COMPUTE STATISTICS;

Using ANALYZE with the COMPUTE STATISTICS option scans the entire table, cluster, or index and computes exact statistics. When you compute the exact statistics, the resultant data is more accurate than that achieved by estimating the statistics; however, you use more system resources to get this information. When you compute statistics for tables and clusters, you must have enough temporary space to load and sort the entire table or cluster. You do not need this temporary space for indexes.

Using ANALYZE to Estimate Statistics

When you use the ANALYZE command to estimate statistics, Oracle does much less work and uses much less temporary space. To run the ANALYZE command to estimate statistics, use one of the following syntaxes.

For Tables:

ANALYZE TABLE table_name

    ESTIMATE STATISTICS;

For Table Partitions:

ANALYZE TABLE table_name PARTITION (partition_name)

    ESTIMATE STATISTICS;

For Clusters:

ANALYZE CLUSTER cluster_name

    ESTIMATE STATISTICS;

For Indexes:

ANALYZE INDEX index_name

    ESTIMATE STATISTICS;

For Index Partitions:

ANALYZE INDEX index_name PARTITION (partition_name)

    ESTIMATE STATISTICS;

When you use ANALYZE with the ESTIMATE STATISTICS option, Oracle scans a portion of the table, cluster, or index and computes estimated statistics. You can specify the amount of data scanned and used for statistics by including one or both of these additional parameters:

SAMPLE xxxx ROWS;

SAMPLE yy PERCENT;

Place the SAMPLE xxxx ROWS parameter at the end of the ANALYZE command, as follows:

ANALYZE TABLE table_name

    ESTIMATE STATISTICS

    SAMPLE 10000 ROWS;

Place the SAMPLE yy PERCENT parameter at the end of the ANALYZE command, as follows:

ANALYZE TABLE table_name

    ESTIMATE STATISTICS

    SAMPLE 40 PERCENT;

Although estimating statistics does not give you as accurate a representation as computing statistics does, the lesser amount of resources consumed usually makes estimating statistics a better choice. By making the percentage of data scanned as large as possible for your system, you can increase the effectiveness of the statistics you gather.

---

Tip:  
When analyzing both tables or indexes and their partitions, analyze the object first, and then the object’s partitions.

---

Using ANALYZE to Check Structural Integrity

In addition to gather statistics, you can use the ANALYZE command to validate the structure of a table, cluster, or index. You should run this command only if you feel there is some problem with the structure of these objects. These problems can occur as the result of a hardware or software problem where data corruption may have occurred. Any problems can be found immediately by analyzing the structure of the schema objects, thus avoiding a system crash.

When you analyze the integrity of the structure of tables, clusters, or indexes, the command returns any structural problems. If there are problems with structure of these objects, you should drop the object, re-create it, and reload the data.

Using ANALYZE to Determine Chained Rows

You can also use the ANALYZE command to determine the extent and existence of chained or migrated rows in your table or cluster. The existence of chained or migrated rows can cause severe performance degradation and should be corrected.