ANALYZE

ANALYZE — generate table and index statistics

Syntax

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

Description

Index statistics are used by the optimizer to improve query performance allowing the optimizer to choose the most efficient index access method.

The ANALYZE command calculates index statistics based on all available record versions, including older record versions that represent deleted or updated rows. Unreferenced older record versions are removed only during a full garbage collection cycle. If your workload significantly changes the data profile (for example truncating a table), the results of the ANALYZE command will not immediately reflect such changes in data profile. If it is necessary to have the results of the ANALYZE command reflect changes to the data profile sooner, use max-full-gc-skips to control the frequency of full garbage collections. For more information on max-full-gc-skips, see Database Options.

Index 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 index statistics for permanent tables. However, indexes statistics on temporary tables are not automatically updated.

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

ANALYZE INDEX generates statistics for the individual index named.
ANALYZE TABLE generates statistics for all indexes on 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 or table.

If the keycount of an index exceeds the statistics collection threshold automatic statistics collection will be disabled and it will be necessary to run ANALYZE periodically to keep the index statistics up to date. If the keycount later decreases below the threshold automatic statistics collection will remain disabled until ANALYZE is run. This can also be used for a single partition of an index.

For information on the current keycount and automatic collection status see the INDEXSTATISTICS System Table.

For information on disabling automatic statistics collection and setting the collection threshold see Database Options.

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.