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.
DB2 10.5 for Linux, UNIX, and Windows