Update system catalog data

If you use the UPDATE STATISTICS statement to update the system catalog before executing a query or other data manipulation language (DML) statement, you can ensure that the information available to the query execution optimizer is current.

In HCL OneDB™, the optimizer determines the most efficient strategy for executing SQL queries and other DML operations. The optimizer allows you to query the database without requiring you 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.

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 sysindices system catalog tables, and in the sysindexes view. (For operations on fragmented tables where the STATLEVEL attribute is set to FRAGMENT, it also updates the sysfragdist and sysfragments 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 and (for fragment-level statistics) the sysfragdist system catalog tables hold the updated column-distribution data.

Whenever you modify a data table extensively, use the UPDATE STATISTICS statement to update data in the system catalog. For more information about the UPDATE STATISTICS statement, see the HCL OneDB Guide to SQL: Syntax.