DB2 Version 9.7 for Linux, UNIX, and Windows

Predicate typology and access plans

A predicate is an element of a search condition that expresses or implies a comparison operation. Predicates, which usually appear in the WHERE clause of a query, are used to reduce the scope of the result set that is returned by the query.

Predicates can be grouped into four categories, depending on how and when they are used in the evaluation process. These categories are listed below, in order of best to worst performance:

Range-delimiting predicates
Index SARGable predicates
Data SARGable predicates
Residual predicates

A SARGable term is a term that can be used as a search argument.

Table 1 summarizes the characteristics of these predicate categories.

Table 1. Summary of Predicate Type Characteristics
Characteristic	Predicate Type
Characteristic	Range-delimiting	Index-SARGable	Data-SARGable	Residual
Reduce index I/O	Yes	No	No	No
Reduce data page I/O	Yes	Yes	No	No
Reduce the number of rows that are passed internally	Yes	Yes	Yes	No
Reduce the number of qualifying rows	Yes	Yes	Yes	Yes

Range-delimiting and index-SARGable predicates

Range-delimiting predicates limit the scope of an index scan. They provide start and stop key values for the index search. Index-SARGable predicates cannot limit the scope of a search, but can be evaluated from the index, because the columns that are referenced in the predicate are part of the index key. For example, consider the following index:

   INDEX IX1:  NAME    ASC,
               DEPT    ASC,
               MGR     DESC,
               SALARY  DESC,
               YEARS   ASC

Consider also a query that contains the following WHERE clause:

   where
     name = :hv1 and
     dept = :hv2 and
     years > :hv5

The first two predicates (name = :hv1 and dept = :hv2) are range-delimiting predicates, and years > :hv5 is an index-SARGable predicate.

The optimizer uses index data instead of reading the base table when it evaluates these predicates. Index-SARGable predicates reduce the number of rows that need to be read from the table, but they do not affect the number of index pages that are accessed.

Data SARGable predicates

Predicates that cannot be evaluated by the index manager, but that can be evaluated by Data Management Services (DMS), are called data-SARGable predicates. These predicates usually require access to individual rows in a table. If required, DMS retrieves the columns that are needed to evaluate a predicate, as well as any other columns that are needed for the SELECT list, but that could not be obtained from the index.

For example, consider the following index that is defined on the PROJECT table:

   INDEX IX0:  PROJNO ASC

In the following query, deptno = 'D11' is considered to be a data-SARGable predicate.

   select projno, projname, respemp
     from project
     where deptno = 'D11'
     order by projno

Residual predicates

Residual predicates are more expensive, in terms of I/O cost, than accessing a table. Such predicates might:

Use correlated subqueries
Use quantified subqueries, which contain ANY, ALL, SOME, or IN clauses
Read LONG VARCHAR or LOB data, which is stored in a file that is separate from the table

Such predicates are evaluated by Relational Data Services (RDS).

Some predicates that are applied only to an index must be reapplied when the data page is accessed. For example, access plans that use index ORing or index ANDing always reapply the predicates as residual predicates when the data page is accessed.