Predicate selectivity overrides
You can provide information to Db2 about predicates that are difficult for Db2 to estimate. Db2 uses the information that you provide during access path selection.
Db2 cannot estimate filter factors for certain types of predicates. It must rely on inaccurate estimates or even default filter factors to select access paths for statements that use such predicates. Examples include predicates that involve the following items, among others:
- Host variables
- Parameter markers
- Expressions
- Table self-joins
- Subqueries
However, you can override these default filter factors for certain predicates by creating selectivity overrides. Each predicate selectivity override describes the selectivity of a particular predicate in a particular SQL statement. When a statement contains more than one predicate, you can create separate selectivity overrides for each predicate in the statement. To create selectivity overrides, you populate a set of input EXPLAIN tables and issue a BIND QUERY command.
The selectivity overrides are added to certain catalog tables, and Db2 uses the information during access path selection.