UPDATE STATISTICS statement

Use the UPDATE STATISTICS statement to update system catalog information that the query optimizer uses for operations on objects in the local database. The UPDATE STATISTICS statement is an extension to the ANSI/ISO standard for SQL.

Syntax

(explicit id upsta003) upsta003 (explicit id upsta004) upsta004 UPDATE STATISTICS { { { LOW <Table and Column Scope> [ DROP DISTRIBUTIONS [ ONLY ] ] | { MEDIUM | HIGH } <Table and Column Scope> [ <Resolution Clause> [] ] } [ { FORCE | AUTO } ] } | <Routine Statistics>[] }

Table and Column Scope

[ FOR TABLE { [ [ 'owner' . ] { table | synonym } [ ( column ) ] ] | [ ONLY ( [ 'owner' . ] { table | synonym } ) [ ( column ) ] ] } ]
Element Description Restrictions Syntax
column A column in table or synonym Must exist. With the MEDIUM or HIGH keyword, the column cannot be of BYTE, LVARCHAR, or TEXT data type. Identifier
owner The owner of table or synonym Must be the owner of table or synonym Owner name
synonym A synonym for a table whose statistics are to be updated The synonym and the table to which it points must exist in the current database Identifier
table Table for which statistics are to be updated Must exist in the current database or be a temporary table created in the current session Identifier

Usage

Use the UPDATE STATISTICS statement to perform any of the following tasks:
  • Calculate the distribution of column values for tables and table fragments.
  • Update system catalog tables that the database server uses to optimize queries.
  • Force reoptimization of SPL routines.
  • Convert existing indexes when you upgrade the database server.

Run the UPDATE STATISTICS statement in a transaction that does not contain any other statements.

The Table and Column Scope clause and the Routine Statistics clause can be empty. If you specify no mode, no table, no routine, and no Resolution clause, the default scope of the UPDATE STATISTICS statement is all the permanent tables in the current database, as in the following example. 
UPDATE STATISTICS;  -- calculates LOW column distribution statistics for 
                    -- all permanent user-defined and system catalog tables
                    -- that the AUTO_STAT_MODE and STATCHANGE settings allow

For more examples of UPDATE STATISTICS statements that specify no identifiers of tables or of SPL routines, see the topic The scope of UPDATE STATISTICS statements. See also the references to AUTO_STAT_MODE and STATCHANGE topics in Performance considerations of UPDATE STATISTICS statements.

The UPDATE STATISTICS statement is not supported on secondary servers within a high-availability cluster.

Restriction: You cannot update the statistics for a table or update the query plan of a UDR in any database except the current database. That is, the database server ignores remote database objects when executing the UPDATE STATISTICS statement.
Important: The statistics that the database server collects might require an sbspace for storage. You can create an sbspace by running the onspaces -c -S command. You must also set the SYSSBSPACENAME configuration parameter to the sbspace name. If the SYSSBSPACENAME configuration parameter is not set, the database server might not be able to store the column distribution statistics, so that the UPDATE STATISTICS statement fails with error -9814, "Invalid default sbspace name".
1 See Resolution Clause
2 See Routine Statistics