Removal of pre-evaluated predicates

Always-false predicates

Db2 sometimes removes predicates that are always evaluated as false. Such predicates are sometimes used by query generators, and in application programs, to toggle between real and fake predicates. In many cases Db2 can remove these predicates to improve the efficiency of the access path for the statement.

Db2 might remove certain always-false predicates, as described by the following examples:

• Equal predicates that contain non-matching constant values: constant-value1 = constant-value2
• IN predicates that match a constant value to a list of non-matching constant values: constant-value1 IN (constant-value2, constant-value3,constant-value3)
• IS NULL predicates for a column that is defined as NOT NULL: non-null-col IS NULL

When Db2 encounters always false predicates in complex predicates that contain OR and AND conditions, it uses certain rules to determine whether to remove always-false predicates. For example:

• Always-false predicates under an OR condition might be removed. For example, consider the following predicate:

  WHERE ('A' = 'B' OR COL1 IN ('B', 'C'))

  Db2 can simplify the predicate by removing the 'A' = 'B' predicate, which is always false. Therefore Db2 can use the following simplified predicate instead:

  WHERE COL1 IN ('B', 'C')

• Db2 does not remove OR 0=1 or OR 0<>0 predicates. Predicates of these forms can be used to prevent index access.
• When an always-false predicate is under an AND condition, the entire AND condition might be removed. When that happens, the change is propagated through all AND ancestors in a predicate tree until an OR predicate or the root of the predicate tree is encountered.
• IS NULL predicates (for NOT NULL columns) under AND conditions are removed. If the AND condition is under an OR condition, the OR condition is also removed. For example, consider the following statement:

  SELECT * FROM T1
  WHERE (C1 IS NULL AND C2 > 123) OR C3 = 54321;

  Db2 can simplify this statement to the following form:

  SELECT * FROM T1 WHERE C3 = 54321;

• When an always-false IS NULL predicate is under an OR condition and the other side contains a host variable or expression, the predicate is not removed.
• Predicates are removed from only WHERE, HAVING, and ON clauses.
• WHEN predicates for CASE expressions are not simplified.
• When either side of an AND or OR condition is a predicate that contains a subquery, the removal of always-false predicates cannot be propagated through the condition, because the subquery cannot be removed.
• Always-false range predicates that compare constants, such as 1 > 2 are not removed.
• Predicates that contains host variables, which might be always-false under REOPT(ALWAYS), are not removed.
• Constant range predicates, such as 1 > 2 are not pre-evaluated as always-false predicates
• Predicates with host variables such as :H1='A' are not pre-evaluated, even under REOPT(VARS) processing rules.
• If one side of an AND or OR predicate is a predicate that involves a subquery, the always-false result cannot be propagated through it.