Default filter factors for simple predicates
DB2® uses default filter factor values when no other statistics exist.
| Predicate Type | Filter Factor |
|---|---|
| Col = constant | 0.04 |
| Col <> constant | 0.96 |
| Col IS NULL | 0.04 |
| Col IS NOT DISTINCT FROM | 0.04 |
| Col IS DISTINCT FROM | 0.96 |
| Col IN (constant-list) | MIN(number-of-constants/25, 1.0) |
| Col Op constant, where Op is one of these operators: <, <=, >, >=. | 0.33 |
| Col LIKE constant | 0.10 |
| Col BETWEEN constant1 and constant2 | 0.10 |
Example
The default filter factor for the predicate C1 = 'D' is 1/25 (0.04). However, If the selectivity of the D value is actually not close to 0.04, the default filter factor probably does not lead to an optimal access path. In such cases, statistics might be needed to improve access path selection.
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.
