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. The following list ranks these categories in order of best to worst performance:
  1. Range-delimiting predicates
  2. Index sargable predicates
  3. Data sargable predicates
  4. 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
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.
Attention: When a jump scan or skip scan is applied, for performance purposes, years > :hv5 is treated as both a range-delimited predicate and 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.