Scope of UPDATE STATISTICS

If you include no Table and Column Scope clause and no Resolution clause, then statistics are updated for every table and SPL routine in the current database, including the system catalog tables. Similarly, if you include a clause that begins with the FOR keyword, but do not specify the name of any table or SPL routine, the database server recalculates distributions for all tables, including temporary tables, or reoptimizes the query plans of all SPL routines in the current database.

If you use the FOR TABLE keywords without also specifying a table name, the database server calculates distributions on all of the tables in the current database, and on all of the temporary tables in your session.

In databases of Extended Parallel Server, the UPDATE STATISTICS statement does not update, maintain, or collect statistics on indexes, and it does not update the syscolumns or sysindexes tables. References to indexes or to the syscolumns or sysindexes system catalog tables in sections that follow do not apply to Extended Parallel Server.

Updating Statistics for Tables

Although a change to the database might make information in the systables, syscolumns, sysindexes, and sysdistrib system catalog tables obsolete, the database server does not automatically update those tables. Issue an UPDATE STATISTICS statement in the following situations to ensure that the stored distribution information reflects the state of the database:

You perform extensive modifications to a table.
An application changes the distribution of column values.
UPDATE STATISTICS reoptimizes queries on the modified objects.
You upgrade a database for use with a newer database server.
The UPDATE STATISTICS statement converts the old indexes to conform to the newer database server index format and implicitly drops the old indexes.

You can convert the indexes table by table or for the entire database at one time. Follow the conversion guidelines in the IBM Informix Migration Guide.

If your application makes many modifications to the data in a particular table, update the system catalog for that table routinely with UPDATE STATISTICS to improve query efficiency. The term many modifications is relative to the resolution of the distributions. If the data modifications have little effect on the distribution of column values, you do not need to execute UPDATE STATISTICS.