SQL Optimizer Statistics

The database system automatically saves basic statistics with information on the size of tables as well as indexes in the internal file directory. On request, the database system also determines a range of additional statistical information, which it saves in the database catalog (this operation is called ”updating the statistics”).

The SQL Optimizer requires the statistics to determine the optimal search strategy for accessing the data in the processing of complex SQL statements with specific search conditions.

Activities

We recommend that you update the statistics once a week. Always update the statistics whenever the size relationships or values in the database instance change considerably.

To update the statistics for a table, the system temporarily sets a shared block on the entry for this table in the database catalog. As long as a shared lock exists for the entry of the table in the database catalog, you cannot change this entry and thus cannot perform any DDL operations on the table. It is also not possible to set an exclusive lock for a table while the statistics of this table are being updated.

To update the statistics, you use the Database Manager or, in SAP systems, the CCMS.

Database Manager GUI, Update Statistics Information

?     Database Manager CLI,

sql_updatestat

auto_update_statistics

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

To exclude a table from the updating of statistics, you select the value 0 for the sample size for this table.

See also:

SQL Optimizer Procedure

SQL Reference Manual, UPDATE STATISTICS Statement (update_statistics_statement)