ANALYZE

ANALYZE — generate table and index statistics

Syntax

ANALYZE { TABLE | STATISTICS } name [ PARTITION partition_name ] [ table_statistics_options ];
ANALYZE INDEX name [ PARTITION partition_name ];
ANALYZE ALL TABLES IN SCHEMA schema [ LIKE pattern ];

Where table_statistics_options can be one of the following:

GENERATE SCALAR STATISTICS USING sampling_option [ GENERATE HISTOGRAM STATISTICS USING sampling_option ]
GENERATE HISTOGRAM STATISTICS USING sampling_option [ HISTOGRAMS ONLY ]
HISTOGRAMS ONLY

Where sampling_option can be one of the following:

FULLSCAN
SAMPLE n {PERCENT|ROWS}

Description

The ANALYZE command can be used to manually update statistics on permanent tables or temporary tables.

ANALYZE TABLE generates all statistics for the table.
ANALYZE INDEX generates scalar and index histogram statistics for the column groups specified by the index.
ANALYZE STATISTICS generates scalar and sampling based histogram statistics for the column groups specified by the statistics.
ANALYZE TABLE … HISTOGRAMS ONLY generates all histogram statistics for the table.
ANALYZE … GENERATE … overrides the default sampling to use when generating statistics for the table.
ANALYZE ALL TABLES IN SCHEMA generates statistics for all tables in a given schema.
ANALYZE … PARTITION generates statistics for a single partition of an index, table, or statistics.

Statistics are used by the optimizer to improve query performance allowing the optimizer to choose the most efficient query plan.

Statistics for permanent tables are automatically updated periodically without any user intervention. As a result, users generally will not need to run the ANALYZE command to update the statistics for permanent tables. However, statistics on temporary tables are not automatically updated.

Sampling

Sampling is used to generate histogram statistics for column groups statistics. For more information, see CREATE STATISTICS.

Scalar statistics are generated using a full scan of the table. This can be overriden by explicitly using the GENERATE SCALAR STATISTICS options, but this is only recommended for test purposes.

Statistics for all columns used by indexes and index prefixes will be generated from the full index. If any index columns overlap with those used by any other table or column group statistics, the index statistics will be used.

If different sampling rates are specified for the column group statistics in a table, the highest sampling rate will be used during an ANALYZE TABLE scan.

Parameters

name: Name of the index or table to analyze.
schema: Name of the schema that contains tables to be analyzed.
LIKE pattern: This supplies filter criteria such that only table names that match the specified pattern will have statistics updated. See SQL Pattern Matching for rules regarding the format of the pattern string.
PARTITION partition_name: Name of the partition of an index or table to analyze.
FULLSCAN|SAMPLE n {PERCENT|ROWS}: The record sampling rate to use when generating sample based statistics. The sampling rate can be controlled by specifying either a specific number of rows or a percentage of the total number. FULLSCAN requests that all rows in the table are scanned during the computation of the statistics. FULLSCAN and SAMPLE 100 PERCENT produce the same result.