DB2 10.5 for Linux, UNIX, and Windows

Predicate processing for queries

A predicate is an element of a search condition that expresses or implies a comparison operation. Predicates can be grouped into four categories that are determined by how and when the predicate is used in the evaluation process.

The categories are provided in the following list, ordered in terms of performance starting with the most favorable:

Range delimiting predicates are those used to bracket an index scan; they provide start or stop key values for the index search. These predicates are evaluated by the index manager.
Index sargable predicates are not used to bracket a search, but are evaluated from the index if one is chosen, because the columns involved in the predicate are part of the index key. These predicates are also evaluated by the index manager.
Data sargable predicates are predicates that cannot be evaluated by the index manager, but can be evaluated by Data Management Services (DMS). Typically, these predicates require the access of individual rows from a base table. If necessary, DMS will retrieve the columns needed to evaluate the predicate, as well as any others to satisfy the columns in the SELECT list that could not be obtained from the index.
Residual predicates are those that require I/O beyond the simple accessing of a base table. Examples of residual predicates include those using quantified subqueries (subqueries with ANY, ALL, SOME, or IN), or reading large object (LOB) data that is stored separately from the table. These predicates are evaluated by Relational Data Services (RDS) and are the most expensive of the four categories of predicates.

The following table provides examples of various predicates and identifies their type based on the context in which they are used.

Note: In these examples, assume that a multi-column ascending index exists on (c1, c2, c3) and is used in evaluating the predicates where appropriate. If any column in the index is in descending order, the start and stop keys might be switched for range delimiting predicates.

Table 1. Predicate processing for different queries
Predicates	Column c1	Column c2	Column c3	Comments	During a jump scan or skip scan
c1 = 1 and c2 = 2 and c3 = 3	Range delimiting (start-stop)	Range delimiting (start-stop)	Range delimiting (start-stop)	The equality predicates on all the columns of the index can be applied as start-stop keys.	Not applicable
c1 = 1 and c2 = 2 and c3 ≥ 3	Range delimiting (start-stop)	Range delimiting (start-stop)	Range delimiting (start)	Columns c1 and c2 are bound by equality predicates, and the predicate on c3 is only applied as a start key.	Not applicable
c1 ≥1 and c2 = 2	Range delimiting (start)	Range delimiting (start-stop)	Not applicable	The leading column c1 has a ≥ predicate and can be used as a start key. The following column c2 has an equality predicate, and therefore can also be applied as a start-stop key.	Not applicable
c1 = 1 and c3 = 3	Range delimiting (start-stop)	Not applicable	Index sargable	The predicate on c3 cannot be used as a start-stop key, because there is no predicate on c2. It can, however, be applied as an index sargable predicate.	If skip scan is used, then there is a predicate on c2. c3 = 3 is applied as both a range delimiting predicate and an index sargable predicate. c3 = 3 is applied as a start-stop key when it can eliminate keys less than (1, x, 3) or greater than (1, y, 3) where x is the minimum value for c2 and y is the maximum value for c2. Otherwise, it is reapplied as an index sargable to eliminate keys such as (1, 2, 4).
c1 = 1 and c2 > 2 and c3 = 3	Range delimiting (start-stop)	Range delimiting (start)	Index sargable	The predicate on c3 cannot be applied as a start-stop predicate because the previous column has a > predicate. Had it been a ≥ instead, you would be able to use it as a start-stop key.	If skip scan is used, the predicate on c3 can be applied as a start-stop predicate despite the previous column with a > predicate. As in the example predicates case of "c1 = 1 and c3 = 3", c3 = 3 can be applied as a start-stop key and also be applied as an index sargable predicate.
c1 = 1 and c2 ≤ 2 and c4 = 4	Range delimiting (start-stop)	Range delimiting (stop)	Data sargable	Here the predicate on c2 is a ≤ predicate. It can be used as a stop key. The predicate on c4 cannot be applied on the index and is applied as a data sargable predicate during the FETCH.	Not applicable
c2 = 2 and UDF_with_ external_action(c4)	Not applicable	Index sargable	Residual	The leading column c1 does not have a predicate, and therefore the predicate on c2 can be applied as an index sargable predicate where the whole index is scanned. The predicate involving the user-defined function with external action is applied as a residual predicate.	As in the example predicates case of "c1 =1 and c3 = 3", c2 = 2 can be applied as a start-stop key and also be applied as an index sargable predicate.
c1 = 1 or c2 = 2	Index sargable	Index sargable	Not applicable	The presence of an OR does not allow us this multi-column index to be used as start-stop keys. This might have been possible had there been two indexes, one with a leading column on c1, and the other with a leading column on c2, and the DB2® optimizer chose an "index-ORing" plan. However, in this case the two predicates are treated as index sargable predicates.	Not applicable
c1 < 5 and (c2 = 2 or c3 = 3)	Range delimiting (stop)	Index sargable	Index sargable	Here the leading column c1 is exploited to stop the index scan from using the predicate with a stop key. The OR predicate on c2 and c3 are applied as index sargable predicates.	Not applicable

The DB2 optimizer employs the query rewrite mechanism to transform many complex user-written predicates into better performing queries, as shown in the following table:

Table 2. Query rewrite predicates
Original predicate or query	Optimized predicates	Comments
c1 between 5 and 10	c1 ≥ 5 and c1 ≤ 10	The BETWEEN predicates are rewritten into the equivalent range delimiting predicates so that they can be used internally as though the user specified the range delimiting predicates.
c1 not between 5 and 10	c1 < 5 or c1 > 10	The presence of the OR predicate does not allow the use of a start-stop key unless the DB2 optimizer chooses an index-ORing plan.
SELECT * FROM t1 WHERE EXISTS (SELECT c1 FROM t2 WHERE t1.c1 = t2.c1)	SELECT t1.* FROM t1 EOJOIN t2 WHERE t1.c1= t2.c1	The subquery might be transformed into a join.
SELECT * FROM t1 WHERE t1.c1 IN (SELECT c1 FROM t2)	SELECT t1* FROM t1 EOJOIN t2 WHERE t1.c1= t2.c1	This is similar to the transformation for the EXISTS predicate example in the previous row.
c1 like 'abc%'	c1 ≥ 'abc X X X ' and c1 ≤ 'abc Y Y Y'	If you have c1 as the leading column of an index, DB2 generates these predicates so that they can be applied as range-delimiting start-stop predicates. Here the characters X and Y are symbolic of the lowest and highest collating character.
c1 like 'abc%def'	c1 ≥ 'abc X X X ' and c1 ≤ 'abc Y Y Y' and c1 like 'abc%def'	This is similar to the previous case, except that you also have to apply the original predicate as an index sargable predicate. This ensures that the characters def match correctly.

Restrictions

The following restrictions are applicable to scenarios that involve indexes with random ordering:

The key part of an index with random ordering cannot use range delimiting predicates to satisfy LIKE, <, <= ,> ,>=, or IS NOT NULL predicates.
Predicates that compare BIGINT or DECIMAL random ordered index column types to REAL or DOUBLE values cannot be applied as start-stop keys on the random ordered index.
Predicates that compare REAL or DOUBLE random ordered index column types to DECFLOAT values cannot be applied as start-stop keys on the random ordered index.