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.
- Range-delimiting predicates
- Index sargable predicates
- Data sargable predicates
- Residual predicates
|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
INDEX IX1: NAME ASC, DEPT ASC, MGR DESC, SALARY DESC, YEARS ASC
The first two predicates (
where name = :hv1 and dept = :hv2 and years > :hv5
name = :hv1 and dept = :hv2) are range-delimiting predicates, and
years > :hv5is an index-sargable predicate.
years > :hv5is 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.
INDEX IX0: PROJNO ASC
deptno = 'D11'is considered to be a data-sargable predicate.
select projno, projname, respemp from project where deptno = 'D11' order by projno
- 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
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.