Optimiser Requirements

The Informix DBMS endeavours to optimise the way that a query is executed. This is both for the user's and its own benefit, to reduce the amount system resources (CPU time, memory and temporary disk space usage), processing (data comparisons) and, ultimately processing time. Optimisation requires knowledge of the tables involved in the query. This from System is obtained the Catalogue tables of the database (or databases) involved. The information includes:

• The number of rows in each table.
• Whether a column is constrained to be unique.
• The indexes that exist on each table, including which columns they involve and whether they are in ascending or descending or and whether or not they are clustered.

These areas of information are not updated automatically as data is updated nor when table structures and indexes are altered. The overheads in doing so would retard the speed of both data and database maintenance. It is the onus of the database administrator to ensure that the System Cataloguel information for a database is updated when extensive modifications to data, indexes or tables are made.

To perform such updates, use the following statement:

UPDATE STATISTICS [[FOR TABLE [tablename]]
FOR PROCEDURE [procedurename]

If the statement is executed without any clauses, the statistical information for all tables and stored procedures within the active database will be updated. If the statement is executed with the FOR TABLE clause, but without any specific table named, the statistical information for all tables within the active database will be updated.

If the statement is executed with the FOR PROCEDURE clause, but without any specific table named, the statistical information for all stored procedures within the active database will be updated.