## The SQL Statistics Statements

The SQL statistics statements UPDATE STATISTICS may be used to collect statistical information in the areas described below. Also refer to the Mimer SQL Reference Manual, UPDATE STATISTICS for details.

Statistics may be collected for the entire database, i.e. all tables in all databanks recorded in the same SYSDB, for tables owned by specified idents, or for specific tables.

The statement DELETE STATISTICS is used to remove the statistics collected. See Mimer SQL Reference Manual, DELETE STATISTICS for details.

## Note: The database remains fully accessible while statistics are being collected (or deleted).

## Statistics for the Entire Database

To collect statistical data for all tables in the database, use the following function:

SQL> UPDATE STATISTICS;The user must have STATISTICS privilege.

## Note: Even in a database of only moderate size, collecting statistical data for all tables is time-consuming. We recommend that you run this option in particular at off-peak times.

## Statistics for Specified Idents

To collect statistics for all base tables belonging to schemas owned by a list of specified idents, use the following function:

SQL> UPDATE STATISTICS FOR IDENT list-of-idents;A user requesting statistics for tables belonging to a schema owned by an ident other than himself must have STATISTICS privilege.

To collect statistics for SYSDB, the pseudo-ident SYSTEM may be specified.

## Statistics for Specified Tables

To collect statistics for a list of specified tables, use the following function:

SQL> UPDATE STATISTICS FOR TABLE list-of-tables;The user requesting statistics for the tables specified in the list must either be the owner of them or have STATISTICS privilege.

## Secondary Index Consistency

The update statistics facility includes an automatic function which ensures that all secondary indexes on tables contained in databanks with the TRANSACTION or LOG option are in a consistent state.

This function is performed in a way that makes it transparent to other users of the database and it is only performed on secondary indexes created on tables actually selected by the UPDATE STATISTICS statement.

It will take some time to verify the consistency of a secondary index. The data dictionary table TABLE_CONSTRAINTS can be used to determine which secondary indexes are flagged as not consistent (shown in the column named IS_CONSISTENT).

An index which is in a consistent state will offer optimal performance when used in a query.

All secondary indexes contained in a databank with the WORK option and those contained in a databank that has been upgraded from Mimer SQL version 7 or 8.1 will be flagged as not consistent.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |