Column Values Distribution Statistics Pane
The Column Values Distribution Statistics pane is displayed on the Access Plan tab in the SQL Optimizer window. It lists statistics used by DB2's SQL optimizer to handle non-uniform distribution of column values. For the column highlighted in the Column Statistics pane, this pane describes the position of column values in the order of most frequently used values or in quantile order. SQL Optimizer obtains this information from the SYSCAT.COLDIST catalog view. The DB2's SQL optimizer uses this information to estimate more accurately the number of rows in a column that satisfy the specified equality or range predicates.
Sequence
The position of the column value (listed for ColValue) within the order of most frequently used values or within the quantile order.
-
If Type is Frequency, the sequence number n identifies the column value as the nthe most frequent value for the column.
-
If Type is Quantile, the sequence number n identifies the column value as the nth quantile value for the column.
ValCount
The count for the column value listed for ColValue. The meaning of this count depends on the type of statistics collected for the column value: frequency or quantile.
-
If Type is Frequency, the value count is the number of rows that contain this value in the column.
-
If Type is Quantile, the value count is the number of rows containing values in the column that are less than or equal to ColValue.
In a partitioned database, ValCount is the estimated value of the count at the database partition multiplied by the number of database partitions. However, when Type is Frequency and the column is the single-column partitioning key of the table, ValCount is simply the count at the database partition.
DistCount
The number of distinct values in the column that are less than or equal to the column value listed under ColValue.
Note: This count is collected only for columns that are the first key column in an index.
Type
The type of distribution statistics collected for the column value listed under ColValue:
|
Value |
Description |
|
Frequency |
The statistics show the values for the highest number of duplicate values in the column. The number of rows displayed is dependent upon the num_freqvalues database configuration parameter or the value that was specified when the table statistics were updated. |
|
Quantile |
Statistics about value’s quantile position in the column are collected. The number of rows displayed is dependent upon the num_quantiles database configuration parameter or the value that was specified when the table statistics were updated.. |
ColValue
The value of data in the column displayed as a character literal or as a NULL value.