Background documentationUPDATE STATISTICS Statement (update_statistics_statement) Locate this document in the navigation structure

 

The UPDATE STATISTICS statement (update_statistics_statement) defines the storage requirements of tables and indexes as well as the value distribution of columns, and stores this information in the database catalog.

Structure

Syntax Syntax

  1. <update_statistics_statement> ::=
  UPDATE STAT[ISTICS] COLUMN <table_name>.<column_name>
    [ESTIMATE [<sample_definition>]]
| UPDATE STAT[ISTICS] COLUMN (<column_name>,...) FOR <table_name>
    [ESTIMATE [<sample_definition>]]
| UPDATE STAT[ISTICS] COLUMN (*) FOR <table_name>
    [ESTIMATE [<sample_definition>]]
| UPDATE STAT[ISTICS] <table_name>
    [ESTIMATE [<sample_definition>]]
| UPDATE STAT[ISTICS] [<schema_name>.][<identifier>]*
    [ESTIMATE [<sample_definition>]]
| UPDATE STAT[ISTICS] AS PER SYSTEM TABLE
    [ESTIMATE [<sample_definition>]
End of the code.
<table_name>

If a table name is specified, the table must be a base table and must not be a temporary table. The current database user must have a privilege for the table.

<column_name>

If a column name is specified, this column must exist in specified table.

If * is specified, all columns in the table are used.

<identifier>*

Specifying <identifier>* has the same effect as specifying the UPDATE STATISTICS statement for all base tables whose table name begins with the identifier and for which the current database user has a privilege.

UPDATE STATISTICS *

The database system administrator can use UPDATE STATISTICS * to execute the UPDATE STATISTICS statement for all base tables, even if the administrator has not been assigned a privilege for these tables.

ESTIMATE

  • If ESTIMATE and a sample_definition are specified, the database system estimates the statistical values by selecting data at random. The number of random selects can be given as a number of rows or as a percentage. The runtime of the UPDATE STATISTICS statement can be considerably reduced by specifying ESTIMATE. In most cases, the precision of the statistical values determined is sufficient.

  • If ESTIMATE is specified without sample_definition, the database system estimates the statistical values by selecting data at random. The number of random selects was defined with the CREATE TABLE or ALTER TABLE statement by means of a sample definition for the specified table. The runtime of the UPDATE STATISTICS statement can be considerably reduced by specifying ESTIMATE. In most cases, the precision of the statistical values determined is sufficient.

  • If ESTIMATE is not specified, the database system determines exact statistical values by considering the complete data of the table. For large tables, the runtime can be considerably long.

UPDATE STATISTICS AS PER SYSTEM TABLE

In the <DATABASE_SYSTEM_ADMINISTRATOR>.SYSUPDSTATWANTED system table, the database system enters the table (columns) for which an UPDATE STATISTICS run appears expedient. The SYSUPDSTATWANTED system table can be updated directly by the database system administrator or database administrators or this system table can be updated by kernel automatism or database procedure SYSCHECKSTATISTICS. However, a database administrator can only insert entries that refer to tables owned by this database administrator.

Only the database system administrator is allowed to use the SQL statement UPDATE STATISTICS AS PER SYSTEM TABLE. In this case, an UPDATE STATISTICS is triggered for all table (columns) contained in the SYSUPDSTATWANTED system table. If the support database parameter UpdateStatParallelServerTask is set to a value >= 0, the system attempts to execute the statistics runs in parallel. Subsequently, the entries in system table SYSUPDSTATWANTED are deleted.

Sample Size 0

You can exclude individual tables from an UPDATE STATISTICS run by setting a sample size of 0 (SAMPLE definition) for these tables with an ALTER TABLE statement.

More Information

EXPLAIN Statement (explain_statement)

Definition of System Tables, SYSUPDSTATWANTED, SYSCHECKSTATISTICSLOG

Database Administration, SQL Optimizer, SQL Optimizer Statistics, EXPLAIN Statement

Database Administration in CCMS, SQL Optimizer in CCMS, Scheduling Updates of the SQL Optimizer Statistics

SAP Note 927882