The UPDATE STATISTICS statement updates the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan. For more information on the specific statistics that the database server keeps in the system catalog tables, see Statistics Held for the Table and Index.
To ensure that the optimizer selects a query plan that best reflects the current state of your tables, run UPDATE STATISTICS at regular intervals.
The following table summarizes when to run different UPDATE STATISTICS statements and provides cross-references to sections containing more information. If you have many tables, you can write a script to generate these UPDATE STATISTICS statements. ISA can generate many of these UPDATE STATISTICS statements for your tables.
| When to Execute | UPDATE STATISTICS Statement | Reference for Details
and Examples |
ISA Generates
Statement |
|---|---|---|---|
| Number of rows has changed significantly
or After migration from previous version of database server |
UPDATE STATISTICS LOW
DROP DISTRIBUTIONS |
Updating Number of Rows or Dropping Data Distributions | No |
| For all columns that are not the leading column of any index | UPDATE STATISTICS LOW | Creating Data Distributions | |
| Queries have non-indexed join columns or filter columns | UPDATE STATISTICS MEDIUM
DISTRIBUTIONS ONLY |
Creating Data Distributions | Yes |
| Queries have an indexed join columns or filter columns | UPDATE STATISTICS HIGH table (leading column in index) | Creating Data Distributions | Yes |
| Queries have a multicolumn indexed defined on join columns or filter columns | UPDATE STATISTICS HIGH table (first differing column in multicolumn index) | Creating Data Distributions | No |
| Queries have a multicolumn indexed defined on join columns or filter columns | UPDATE STATISTICS low table (all columns in multicolumn index) | Creating Data Distributions | No |
| Queries have many small tables (fit into one extent) | UPDATE STATISTICS HIGH on small tables | Creating Data Distributions | No |
| Queries use SPL routines | UPDATE STATISTICS for procedure | Reoptimizing SPL Routines | No |