Using the LOW mode option

Use the LOW option of the UPDATE STATISTICS statement to generate and update some of the relevant statistical data regarding table, row, and page-count statistics in the systables system catalog table. If you do not specify any mode, the LOW mode is the default.

In HCL OneDB™, the LOW mode also generates and updates some index and column statistics for specified columns in the syscolumns and the sysindexes system catalog tables.

The LOW mode generates the least amount of information about the column. If you want the UPDATE STATISTICS statement to do minimal work, specify a column that is not part of an index. The colmax and colmin values in syscolumns are not updated unless there is an index on the column.

The following example updates statistics on the customer_num column of the customer table:

UPDATE STATISTICS LOW FOR TABLE customer (customer_num);

Because the LOW mode option does not update data in the sysdistrib system catalog table, all distributions associated with the customer table remain intact, even those that already exist on the customer_num column.

You can set the USTLOW_SAMPLE configuration parameter or the USTLOW_SAMPLE option of the SET ENVIRONMENT statement to enable sampling during the gathering of index statistics for UPDATE STATISTICS operations in LOW mode, as in the following example:

SET ENVIRONMENT USTLOW_SAMPLE ON;
UPDATE STATISTICS LOW FOR TABLE items (quantity,total_price);

If the USTLOW_SAMPLE configuration parameter value is zero, the default behavior for all sessions of the server instance is for LOW mode sampling to be disabled. In this example, however, the UPDATE STATISTICS LOW statement uses sampling, because the ON setting in the preceding SET ENVIRONMENT USTLOW_SAMPLE statement overrides the USTLOW_SAMPLE setting for subsequent LOW mode statistical operations in the same session. For more information, see USTLOW_SAMPLE environment option.