Filter factors for uniform distributions
In certain situations Db2 assumes that a data is distributed uniformly and calculates filter factors accordingly.
Db2 uses
the filter factors in the following table if the following conditions
are true:
- The value in column COLCARDF of catalog table SYSIBM.SYSCOLUMNS
for the column
Col
is a positive value. - No additional statistics exist for
Col
in SYSIBM.SYSCOLDIST.
| Predicate type | Filter factor |
|---|---|
| Col = constant | 1/COLCARDF |
| Col <> constant | 1 – (1/COLCARDF) |
| Col IS NULL | 1/COLCARDF |
| Col IS NOT DISTINCT FROM | 1/COLCARDF |
| Col IS DISTINCT FROM | 1 – (1/COLCARDF) |
| Col IN (constant list) | number of constants / COLCARDF |
| Col Op11 constant | interpolation formula |
| Col Op22 constant | interpolation formula |
| Col LIKE constant | interpolation formula |
| Col BETWEEN constant1 and constant2 | interpolation formula |
Notes:
If you know
that default filter factor does not match the selectivity of a predicate,
you can also create weighted selectivity overrides to improve access
selection.- Op1 is < or <=, and the constant is not a host variable.
- Op2 is > or >=, and the constant is not a host variable.
Example
If D is one of only five values in column C1, using RUNSTATS puts the value 5 in column COLCARDF of SYSCOLUMNS. If no additional statistics are available, the filter factor for the predicate C1 = 'D' is 1/5 (0.2).
Filter factors for other predicate types:
Examples above represent only the most common types
of predicates. If P1 is a predicate and F is its filter factor, then
the filter factor of the predicate NOT P1 is (1 - F). But, filter
factor calculation is dependent on many things, so a specific filter
factor cannot be given for all predicate types. 