In Informix database servers, the optimizer determines the most efficient strategy for executing SQL queries. The optimizer allows you to query the database without having to consider fully which tables to search first in a join or which indexes to use. The optimizer uses information from the system catalog to determine the best query strategy.
If you use the UPDATE STATISTICS statement to update the system catalog before executing a query, you can ensure that the information provided to the optimizer is current. When you delete or modify a table, the database server does not automatically update the related statistical data in the system catalog. For example, if you delete one or more rows in a table with the DELETE statement, the nrows column in the systables system catalog table, which holds the number of rows for that table, is not updated automatically.
The UPDATE STATISTICS statement causes the database server to recalculate data in the systables, sysdistrib, syscolumns, and sysindexes (sysindices for Dynamic Server) system catalog tables. After you run UPDATE STATISTICS, the systables system catalog table holds the correct value in the nrows column. If you specify MEDIUM or HIGH mode when you run UPDATE STATISTICS, the sysdistrib system catalog table holds the updated data-distribution data.
Whenever you modify a data table extensively, use the UPDATE STATISTICS statement to update data in the system catalog. For more information on the UPDATE STATISTICS statement, see the IBM Informix: Guide to SQL Syntax.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]