UPDATE STATISTICS

Updates the statistics recorded for all tables in the database, a specified list of tables, all tables in a specified list of schemas or all the tables belonging to the schemas owned by a specified list of idents.

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

The default operation is to update statistics for all tables, including data dictionary tables, in the database.

It is possible to update statistics for a specified list of tables by using the FOR TABLE option, for all tables belonging to a specified list of schemas by using the FOR SCHEMA option, or for all the tables belonging to the schemas created by a specified list of idents by using the FOR IDENT option.

Update statistics includes an automatic operation which ensures the consistency of secondary indexes (both explicitly created indexes and those created by the system when certain constraints are defined). The operation is transparent to users of the database and is performed on indexes selected by the UPDATE STATISTICS statement that are contained in a databank with the TRANSACTION or LOG option and which are flagged as ‘not consistent’.

The process of ensuring the consistency of an index, and updating statistics for all tables in the database (the default operation), can be rather time-consuming. Therefore, it is generally recommended that these operations be performed at off-peak times, refer to the System Management Manual, Database Statistics for more information.

A secondary index is flagged as not consistent if it belongs to a table in a databank with the WORK option, or if the databank containing it has been upgraded from Mimer SQL version 8.1 (or older).

The IS_CONSISTENT column in the data dictionary table TABLE_CONSTRAINTS shows which indexes in the database are flagged as not consistent.

Restrictions

The current ident must be the creator of all the tables involved or must have STATISTICS privilege.

Notes

The UPDATE STATISTICS statement can be used concurrently with other SQL statements.

Precompiled statements may change search orders depending on the result of the updated statistics.

Example

UPDATE STATISTICS FOR IDENT joe;

 

For more information, see the System Management Manual, Database Statistics.

Standard Compliance