Boolean term predicates

You can improve the performance of queries by choosing Boolean term predicates over non-Boolean term predicates for join operations whenever possible.

Begin program-specific programming interface information. A Boolean term predicate is a simple or compound predicate that, when it is evaluated false for a particular row, makes the entire WHERE clause false for that particular row.

For example, in the following query P1, P2 and P3 are simple predicates:

SELECT * FROM T1 WHERE P1 AND (P2 OR P3);

P1 is a simple Boolean term predicate.
P2 and P3 are simple non-Boolean term predicates.
P2 OR P3 is a compound Boolean term predicate.
P1 AND (P2 OR P3) is a compound Boolean term predicate.

Single index processing generally requires Boolean term predicates for matching index access. Db2 rewrites simple non-Boolean term OR conditions against a single column to use Boolean term IN-lists. For example, the following statement is rewritten:

SELECT * FROM T1 WHERE C1 = ? OR C1 = ?;

The following statement is the result:

SELECT * FROM T1 WHERE C1 IN (?, ?)

More complex Boolean term predicates might be candidates for multi-index access or range list access.

In join operations, Boolean term predicates can reject rows at an earlier stage than can non-Boolean term predicates.

Recommendation: For join operations, choose Boolean term predicates over non-Boolean term predicates whenever possible.

End program-specific programming interface information.