View statistics that are relevant to optimization

Only statistics that characterize the data distribution of the query that defines a statistical view, such as CARD and COLCARD, are considered during query optimization.

The following statistics that are associated with view records can be collected for use by the optimizer.
  • Table statistics (SYSCAT.TABLES, SYSSTAT.TABLES)
    • CARD - Number of rows in the view result
  • Column statistics (SYSCAT.COLUMNS, SYSSTAT.COLUMNS)
    • COLCARD - Number of distinct values of a column in the view result
    • AVGCOLLEN - Average length of a column in the view result
    • HIGH2KEY - Second highest value of a column in the view result
    • LOW2KEY - Second lowest value of a column in the view result
    • NUMNULLS - Number of null values in a column in the view result
    • SUB_COUNT - Average number of sub-elements in a column in the view result
    • SUB_DELIM_LENGTH - Average length of each delimiter separating sub-elements
  • Column distribution statistics (SYSCAT.COLDIST, SYSSTAT.COLDIST)
    • DISTCOUNT - Number of distinct quantile values that are less than or equal to COLVALUE statistics
    • SEQNO - Frequency ranking of a sequence number to help uniquely identify a row in the table
    • COLVALUE - Data value for which frequency or quantile statistics are collected
    • VALCOUNT - Frequency with which a data value occurs in a view column; or for quantiles, the number of values that are less than or equal to the data value (COLVALUE)

Statistics that do not describe data distribution (such as NPAGES and FPAGES) can be collected, but are ignored by the optimizer.