Storing Maximum and Minimum Values

The colmin and colmax values hold the second-smallest and second-largest data values in the column, respectively. For example, if the values in an indexed column are 1, 2, 3, 4, and 5, the colmin value is 2 and the colmax value is 4. Storing the second-smallest and second-largest data values lets the query optimizer make assumptions about the range of values in the column and, in turn, further refine search strategies.

The colmin and colmax columns contain values only if the column is indexed and the UPDATE STATISTICS statement has explicitly or implicitly calculated the column distribution. If you store BYTE or TEXT data in the tblspace, the colmin value is encoded as -1.

The colmin and colmax columns are valid only for data types that fit into four bytes: SMALLFLOAT, SMALLINT, INTEGER, and the first four bytes of CHAR. The values for all other noninteger column types are the initial four bytes of the maximum or minimum value, which are treated as integers.

It is better to use UPDATE STATISTICS MEDIUM than to depend on colmin and colmax values. UPDATE STATISTICS MEDIUM gives better information and is valid for all data types.

HCL OneDB™ does not calculate colmin and colmax values for user-defined data types. These columns, however, have values for user-defined data types if a user-defined secondary access method supplies them.