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
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
".