## 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 |