Storing Maximum and Minimum Values
The colmin and colmax are statistical values which give the second-smallest and
second-largest values of a column at the time of the last update statistics. These values will be
determined by update statistics for leading index columns only. 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 Informix® 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.