Summary of predicate processing

Processing order

Predicates are applied by stage in the following order:

1. Indexable predicates that match on index key columns are applied and evaluated when the index is accessed.
2. Stage 1 index screening predicates that have not been picked as index matching predicates but still refer to index columns, are applied to the index.
3. Stage 1 page range screening predicates refer to partitioning columns are applied to limit the number of partitions that are accessed.
4. Other stage 1 predicates are applied to the data, after data page access.
5. The stage 2 predicates are applied on the returned data rows.

The STAGE column of DSN_FILTER_TABLE indicates the stage at which a predicate was applied.

Within each stage after the indexable stage, predicates are applied in the following order, by type:

1. Equality predicates (including IN predicates that contain only a single item and BETWEEN predicates that contain the same value twice)
2. Range predicates and predicates of the form column IS NOT NULL
3. Other predicate types

After both sets of rules are applied, predicates are evaluated in the order in which they appear in the query. Because you specify that order, you have some control over the order of evaluation. However, Regardless of coding order, non-correlated subqueries are evaluated before correlated subqueries, unless Db2 correlates, de-correlates, or transforms the subquery into a join.

Predicate types and processing by stage

In general, if you form a compound predicate by combining several simple predicates with OR operators, the result of the operation has the same characteristics as the simple predicate that is evaluated latest. For example, if two indexable predicates are combined with an OR operator, the result is indexable. If a stage 1 predicate and a stage 2 predicate are combined with an OR operator, the result is stage 2.

Indexable and stage 1 predicates ³¹

The following predicates might be evaluated by matching index access, during index screening, or after data page access during stage 1 processing.

• COL = value ^{16, 31}
• COL = noncol expr ^{9, 11, 12, 15, 29, 31, 32}
• COL IS NULL ^{20, 21}
• COL op value ^{13, 31}
• COL op noncol expr ^{9, 11, 12, 13, 29, 31, 32}
• value BETWEEN COL1 AND COL2 ^{13, 32}
• COL BETWEEN value1 AND value2 ¹³
• COL BETWEEN noncol expr 1 AND noncol expr 2 ^{9, 11, 12, 13, 23, 29}
• COL BETWEEN expr-1 AND expr-2 ^{6, 7, 11, 12, 13, 14, 15, 27, 29}
• COL LIKE 'pattern' ²⁹
• COL IN (list) ^{17, 18}
• COL IS NOT NULL ²¹
• COL LIKE host variable ^{2, 29}
• COL LIKE UPPER ('pattern') ²⁹
• COL LIKE UPPER (host-variable) ^{2, 29}
• COL LIKE UPPER (SQL-variable)^{2, 29}
• COL LIKE UPPER (global-variable)^{2, 29}
• COL LIKE UPPER (CAST ('pattern' AS data-type))^{2, 29}
• COL LIKE UPPER (CAST (host-variable AS data-type))^{2, 29}
• COL LIKE UPPER (CAST (SQL-variable AS data-type))^{2, 29}
• COL LIKE UPPER (CAST (global-variable AS data-type))^{2, 29}
• ^{2, 29}
• T1.COL = T2.COL
• T1.COL op T2.COL
• T1.COL = T2 col expr ^{6, 9, 11, 12, 14, 15, 25, 27, 29}
• T1.COL op T2 col expr ^{6, 9, 11 , 12, 13, 14, 15, 29}
• COL = (noncor subq)
• COL op (noncor subq) ²⁸
• COL = ANY (noncor subq) ^{22, 29}
• (COL1,...COLn) IN (noncor subq) ²⁹
• COL = ANY (cor subq) ^{19, 22, 29}
• COL IS NOT DISTINCT FROM value ¹⁶
• COL IS NOT DISTINCT FROM noncol expr ^{9, 11, 12, 15, 29}
• T1.COL1 IS NOT DISTINCT FROM T2.COL2 ^{3, 4}
• T1.COL1 IS NOT DISTINCT FROM T2 col expr ^{6, 9, 11, 12, 14, 15, 29}
• COL IS NOT DISTINCT FROM (noncor subq)
• SUBSTR(COL,1,n) = value
• SUBSTR(COL,1,n) op value
• DATE(COL) = value³³
• DATE(COL) op value33
• YEAR(COL) = value³³
• YEAR(COL) op value³³

Stage 1 not indexable predicates ³¹

The following predicates might be evaluated during stage 1 processing, during index screening, or after data page access.

• COL <> value ^{8, 11}
• COL <> noncol expr ^{8, 11, 29}
• COL NOT BETWEEN value1 AND value2
• COL NOT IN (list)
• COL NOT LIKE ' char' ²⁹
• COL LIKE '%char' ^{1, 29}
• COL LIKE '_char' ^{1, 29}
• T1.COL <> T2 col expr ^{8, 11, 27, 29}
• COL op ANY (noncor subq) ²²
• COL op ALL (noncor subq)
• COL IS DISTINCT FROM value ^{8, 11}
• COL IS DISTINCT FROM (noncor subq)

Stage 2 predicates

The following predicates must be processed during stage 2, after the data is returned.

• COL BETWEEN COL1 AND COL2 ¹⁰
• value NOT BETWEEN COL1 AND COL2
• value BETWEEN col expr and col expr³²
• T1.COL <> T2.COL
• T1.COL1 = T1.COL2 ^3,25
• T1.COL1 op T1.COL2 ³
• T1.COL1 <> T1.COL2 ³
• COL = ALL (noncor subq)
• COL <> (noncor subq) ²²
• COL <> ALL (noncor subq)
• COL NOT IN (noncor subq)
• COL = (cor subq) ⁵
• COL = ALL (cor subq)
• COL op (cor subq) ⁵
• COL op ANY (cor subq) ²²
• COL op ALL (cor subq)
• COL <> (cor subq) ⁵
• COL <> ANY (cor subq) ¹⁹
• (COL1,...COLn) IN (cor subq)
• COL NOT IN (cor subq)
• (COL1,...COLn) NOT IN (cor subq)
• T1.COL1 IS DISTINCT FROM T2.COL2 ³
• T1.COL1 IS DISTINCT FROM T2 col expr ^{8, 11}
• COL IS NOT DISTINCT FROM (cor subq)
• EXISTS (subq)¹⁹
• expression = value ^{27, 32}
• expression <> value ²⁷
• expression op value ^{27, 32}
• expression op (subq)
• NOT XMLEXISTS
• CASE expression WHEN expression ELSE expression END = value ³²

Indexable but not stage 1 predicates

The following predicates can be processed during index access, but cannot be processed during stage 1.

• XMLEXISTS ²⁶