Examples of predicate properties

The included examples can help you to understand how and at which stage Db2 processes different predicates.

Begin program-specific programming interface information. Assume that predicate P1 and P2 are simple, stage 1, indexable predicates:

  • P1 AND P2 is a compound, stage 1, indexable predicate.
  • P1 OR P2 is a compound, stage 1 predicate, not indexable except by a union of RID lists from two indexes.

The following examples of predicates illustrate the general rules of predicate processing. In each case, assume that an index has been created on columns (C1, C2, C3, and C4) of the table and that 0 is the lowest value in each column.

WHERE C1=5 AND C2=7
Both predicates are stage 1 and the compound predicate is indexable. A matching index scan could be used with C1 and C2 as matching columns.
WHERE C1=5 AND C2>7
Both predicates are stage 1 and the compound predicate is indexable. A matching index scan could be used with C1 and C2 as matching columns.
WHERE C1>5 AND C2=7
Both predicates are stage 1, but only the first matches the index. A matching index scan could be used with C1 as a matching column.
WHERE C1=5 OR C2=7
Both predicates are stage 1 but not Boolean terms. The compound is indexable. Multiple-index access for the compound predicate is not possible because no index has C2 as the leading column. For single-index access, C1 and C2 can be only index screening columns.
WHERE C1=5 OR C2<>7
The first predicate is indexable and stage 1, and the second predicate is stage 1 but not indexable. The compound predicate is stage 1 and not indexable.
WHERE C1>5 OR C2=7
Both predicates are stage 1 but not Boolean terms. The compound is indexable. Multiple-index access for the compound predicate is not possible because no index has C2 as the leading column. For single-index access, C1 and C2 can be only index screening columns.
WHERE C1 IN (cor subq) AND C2=C1
As written, both predicates are stage 2 and not indexable. The index is not considered for matching-index access, and both predicates are evaluated at stage 2. However, Db2 might transform the correlated subquery to a non-correlated subquery during processing, in which case both predicates become indexable and stage 1
WHERE C1=5 AND C2=7 AND (C3 + 5) IN (7,8)
The first two predicates only are stage 1 and indexable. The index is considered for matching-index access, and all rows satisfying those two predicates are passed to stage 2 to evaluate the third predicate.
WHERE C1=5 OR C2=7 OR (C3 + 5) IN (7,8)
The third predicate is stage 2. The compound predicate is stage 2 and all three predicates are evaluated at stage 2. The simple predicates are not Boolean terms and the compound predicate is not indexable.
WHERE C1=5 OR (C2=7 AND C3=C4)
The third predicate is stage 2. The two compound predicates (C2=7 AND C3=C4) and (C1=5 OR (C2=7 AND C3=C4)) are stage 2. All predicates are evaluated at stage 2.
WHERE (C1>5 OR C2=7) AND C3 = C4
The compound predicate (C1>5 OR C2=7) is indexable and stage 1. The simple predicate C3=C4 is not stage1; so the index is not considered for matching-index access. Rows that satisfy the compound predicate (C1>5 OR C2=7) are passed to stage 2 for evaluation of the predicate C3=C4.
WHERE C1= 17 and C2 <> 100
In this example, assuming that a RANDOM ordering option has been specified on C2 in the CREATE INDEX statement, the query can use the index only in a limited way. The index is an effective filter on C1, but it would not match on C2 because of the random values. The index is scanned for all values where C1=17 and only then ensure that values for C2 are not equal to 100.
WHERE (C1 = 1 OR C2 = 1) AND XMLEXISTS('/a/b[c = 1]' PASSING XML_COL1) AND XMLEXISTS('/a/b[(e = 2 or f[g] = 3) and /h/i[j] = 4]' PASSING XML_COL2)
The compound predicate (C1 = 1 OR C2 = 1) is indexable and stage 1. The first XMLEXISTS predicate is indexable and can become a matching predicate if the XML index /a/b/c has been created. The second XMLEXISTS predicate is indexable and can use multiple index access if the XML indexes, /a/b/e, /a/b/f/g, and /a/b/h/i/j, can be used to evaluate three XPath segments in the predicate. All rows satisfying the three indexable predicates (one compound and two XMLEXISTS) are passed to stage 2 to evaluate the same first and second XMLEXISTS predicates again. End program-specific programming interface information.