The SQL Statistics Statements
The SQL statistics statements
UPDATE STATISTICSmay 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.
DELETE STATISTICSis 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
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
To collect statistics for
SYSDB, the pseudo-ident
SYSTEMmay 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
Secondary Index Consistency
The update statistics facility includes an automatic function which ensures that all secondary indexes on tables contained in databanks with the
LOGoption 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
It will take some time to verify the consistency of a secondary index. The data dictionary table
TABLE_CONSTRAINTScan be used to determine which secondary indexes are flagged as
not consistent(shown in the column named
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
WORKoption and those contained in a databank that has been upgraded from Mimer SQL version 7 or 8.1 will be flagged as
Mimer Information Technology AB
Phone: +46 18 780 92 00