The SQL statistics statements collect statistical information about table and index data in the database and store this information in the data dictionary.
The information is used by the SQL compiler in optimizing access paths for SQL queries.
The statistical information includes:
•the total number of rows in each base table
•the number of distinct values in each column of a table
•the number of non-null values in each column of a table
•the lowest and highest values in each column of a table
The user executing the SQL statistics statements must either have STATISTICS privilege or be the owner of the table(s) or ident(s) for which statistics are being collected.
The database administration ident SYSADM holds STATISTICS privilege with the WITH GRANT OPTION, and may thus take responsibility for maintaining statistics for the whole system or delegate the responsibility to selected idents.
Note:A user with STATISTICS privilege is not necessarily permitted to read the contents of the databank using data manipulation statements, this privilege only permits access for the collection of statistics.
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.
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.
When to Use the SQL Statistics Statements
Mimer SQL collects basic statistics for each table whenever the table is opened. These statistics may suffice for maintaining high performance in many situations.
If optimal performance is required for an application, the SQL statistics statements should be used to collect detailed information (this includes information on value distribution and table size).
When this is the case, statistics should be typically updated in the following situations:
•when the size of a table has changed significantly
•when the maximum/minimum limits on values in a table have altered significantly
•when a databank has been altered from having the WORK option to having the TRANSACTION or LOG option and contains secondary indexes
•when a databank with the TRANSACTION or LOG option contains secondary indexes and has just been upgraded from Mimer SQL version 7 or 8.1.
The statistics information in the data dictionary is used only by the Mimer SQL compiler.
Note:Only the performance, not the result, of an SQL statement is affected by gathering and using the statistical information and by ensuring the consistency of secondary indexes.