Summary of predicate processing

You can improve performance of your SQL statements by specifying predicates that are evaluated at earlier stages.

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

Begin program-specific programming interface information.
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 31
The following predicates might be evaluated by matching index access, during index screening, or after data page access during stage 1 processing.
  • COL = value 17, 31
  • COL = noncol expr 9, 11, 12, 15, 16, Start of change30End of change, 31
  • COL IS NULL 21, 22
  • COL op value 13, 31
  • COL op noncol expr 9, 11, 12, 13, Start of change30End of change, 31
  • COL BETWEEN value1 AND value2 13
  • COL BETWEEN noncol expr 1 AND noncol expr 2 9, 11, 12, 13, 24, Start of change30End of change
  • COL BETWEEN expr-1 AND expr-2 6, 7, 11, 12, 13, 14, 15, 16, 28, Start of change30End of change
  • COL LIKE 'pattern' Start of change30End of change
  • COL IN (list) 18, 19
  • COL IS NOT NULL 22
  • COL LIKE host variable 2, Start of change30End of change
  • Start of changeCOL LIKE UPPER ('pattern') Start of change30End of changeEnd of change
  • Start of changeCOL LIKE UPPER (host-variable) 2, Start of change30End of changeEnd of change
  • Start of changeCOL LIKE UPPER (SQL-variable)2, 30End of change
  • Start of changeCOL LIKE UPPER (CAST ('pattern' AS data-type))2, 30End of change
  • Start of changeCOL LIKE UPPER (CAST (host-variable AS data-type))2, 30End of change
  • Start of changeCOL LIKE UPPER (CAST (SQL-variable AS data-type))2, 30End of change
  • Start of change2, 30End of change
  • Start of changeT1.COL = T2.COLEnd of change
  • Start of changeT1.COL op T2.COLEnd of change
  • T1.COL = T2 col expr 6, 9, 11, 12, 14, 15,16, 26, 28, Start of change30End of change
  • T1.COL op T2 col expr 6, 9, 11 , 12, 13, 14, 15, 16, Start of change30End of change
  • COL = (noncor subq)
  • Start of changeCOL op (noncor subq) 29End of change
  • COL = ANY (noncor subq) 23, Start of change30End of change
  • (COL1,...COLn) IN (noncor subq) Start of change30End of change
  • COL = ANY (cor subq) 20, 23, Start of change30End of change
  • COL IS NOT DISTINCT FROM value 17
  • COL IS NOT DISTINCT FROM noncol expr 9, 11, 12, 15,16, Start of change30End of change
  • Start of changeT1.COL1 IS NOT DISTINCT FROM T2.COL2 3, 4End of change
  • T1.COL1 IS NOT DISTINCT FROM T2 col expr 6, 9, 11, 12, 14, 15,16, Start of change30End of change
  • COL IS NOT DISTINCT FROM (noncor subq)
Stage 1 not indexable predicates 31
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, Start of change30End of change
  • COL NOT BETWEEN value1 AND value2
  • COL NOT IN (list)
  • COL NOT LIKE ' char' Start of change30End of change
  • COL LIKE '%char' 1, Start of change30End of change
  • COL LIKE '_char' 1, Start of change30End of change
  • T1.COL <> T2 col expr 8, 11, 28, Start of change30End of change
  • COL op ANY (noncor subq) 23
  • 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.
  • value BETWEEN COL1 AND COL2
  • COL BETWEEN COL1 AND COL2 10
  • value NOT BETWEEN COL1 AND COL2
  • Start of changevalue BETWEEN col expr and col exprEnd of change
  • Start of changeT1.COL <> T2.COLEnd of change
  • T1.COL1 = T1.COL2 3,26
  • T1.COL1 op T1.COL2 3
  • T1.COL1 <> T1.COL2 3
  • COL = ALL (noncor subq)
  • COL <> (noncor subq) 23
  • COL <> ALL (noncor subq)
  • COL NOT IN (noncor subq)
  • COL = (cor subq) 5
  • COL = ALL (cor subq)
  • COL op (cor subq) 5
  • COL op ANY (cor subq) 23
  • COL op ALL (cor subq)
  • COL <> (cor subq) 5
  • COL <> ANY (cor subq) 20
  • (COL1,...COLn) IN (cor subq)
  • COL NOT IN (cor subq)
  • (COL1,...COLn) NOT IN (cor subq)
  • T1.COL1 IS DISTINCT FROM T2.COL2 3
  • Start of changeT1.COL1 IS DISTINCT FROM T2 col expr 8, 11End of change
  • COL IS NOT DISTINCT FROM (cor subq)
  • EXISTS (subq)20
  • expression = value 28
  • expression <> value 28
  • expression op value 28
  • expression op (subq)
  • NOT XMLEXISTS
Indexable but not stage 1 predicates
The following predicates can be processed during index access, but cannot be processed during stage 1.
  • XMLEXISTS 27
Notes:
  1. Indexable only if an ESCAPE character is specified and used in the LIKE predicate. For example, COL LIKE '+%char' ESCAPE '+' is indexable.
  2. Start of changeIndexable only if the pattern in the variable is an indexable constant (for example, variable='char%'). End of change
  3. If both COL1 and COL2 are from the same table, access through an index on either one is not considered for these predicates. However, the following query is an exception:
    SELECT *  FROM T1 A, T1 B WHERE A.C1 = B.C2;
    By using correlation names, the query treats one table as if it were two separate tables. Therefore, indexes on columns C1 and C2 are considered for access.
  4. Start of changeThe predicate might be indexable and stage 1, if both sides contain the same data type. Otherwise, the predicate is stage 2.End of change
  5. If the subquery has already been evaluated for a given correlation value, then the subquery might not have to be reevaluated.
  6. The column on the left side of the join sequence must be in a different table from any columns on the right side of the join sequence.
  7. The tables that contain the columns in expression1 or expression2 must already have been accessed.
  8. The processing for WHERE NOT COL = value is like that for WHERE COL <> value, and so on.
  9. If noncol expr, noncol expr1, or noncol expr2 is a noncolumn expression of one of these forms, then the predicate is not indexable:
    • noncol expr + 0
    • noncol expr - 0
    • noncol expr * 1
    • noncol expr / 1
    • noncol expr CONCAT empty string
  10. COL, COL1, and COL2 can be the same column or different columns. The columns are in the same table.
  11. Any of the following sets of conditions make the predicate stage 2:
    • Start of changeThe first value obtained before the predicate is evaluated is BIGINT or DECIMAL(p,s), where p>15, and the second value obtained before the predicate is evaluated is REAL or FLOAT.End of change
    • The first value obtained before the predicate is evaluated is CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC, and the second value obtained before the predicate is evaluated is DATE, TIME, or TIMESTAMP.
  12. The predicate is stage 1 but not indexable if the first value obtained before the predicate is evaluated is CHAR or VARCHAR, the second value obtained before the predicate is evaluated is GRAPHIC or VARGRAPHIC, and the first value obtained before the predicate is evaluated is not Unicode mixed.
  13. If both sides of the comparison are strings, any of the following sets of conditions makes the predicate stage 1 but not indexable:
    • The first value obtained before the predicate is evaluated is CHAR or VARCHAR, and the second value obtained before the predicate is evaluated is GRAPHIC or VARGRAPHIC.
    • Both of the following conditions are true:
      • Both sides of the comparison are CHAR or VARCHAR, or both sides of the comparison are BINARY or VARBINARY
      • The length the first value obtained before the predicate is evaluated is less than the length of the second value obtained before the predicate is evaluated.
    • Both of the following conditions are true:
      • Both sides of the comparison are GRAPHIC or VARGRAPHIC.
      • The length of the first value obtained before the predicate is evaluated is less than the length of the second value obtained before the predicate is evaluated.
    • Both of the following conditions are true:
      • The first value obtained before the predicate is evaluated is GRAPHIC or VARGRAPHIC, and the second value obtained before the predicate is evaluated is CHAR or VARCHAR.
      • The length of the first value obtained before the predicate is evaluated is less than the length of the second value obtained before the predicate is evaluated.
  14. If both sides of the comparison are strings, but the two sides have different CCSIDs, the predicate is stage 1 and indexable only if the first value obtained before the predicate is evaluated is Unicode and the comparison does not meet any of the conditions in note 13.
  15. If col expr or noncol expr is a CASE expression, the predicate is stage 2.
  16. If all of the following conditions are true, the predicate is stage 2:
    • col expr or noncol expr is an integer value that is the product or the quotient of two non-column expressions
    • COL is a FLOAT or a DECIMAL column
  17. If COL has the ROWID data type, DB2 tries to use direct row access instead of index access or a table space scan.
  18. If COL has the ROWID data type, and an index is defined on COL, DB2 tries to use direct row access instead of index access.
  19. IN-list predicates are indexable and stage 1 if the following conditions are true:
    • The IN list contains only simple items. For example, constants, host variables, parameter markers, and special registers.
    • The IN list does not contain any aggregate functions or scalar functions.
    • The IN list is not contained in a trigger's WHEN clause.
    • For numeric predicates where the left side column is DECIMAL with precision greater than 15, none of the items in the IN list are FLOAT.
    • For string predicates, the coded character set identifier is the same as the identifier for the left side column.
    • For DATE, TIME, and TIMESTAMP predicates, the left side column must be DATE, TIME, or TIMESTAMP.
  20. Certain predicates might become indexable and stage 1 depending on how they are transformed during processing.
  21. The predicate types COL IS NULL and COL IS NOT NULL are stage 2 predicates when they query a column that is defined as NOT NULL.
  22. If the predicate type is COL IS NULL and the column is defined as NOT NULL, the table is not accessed because C1 cannot be NULL.
  23. The ANY and SOME keywords behave similarly. If a predicate with the ANY keyword is not indexable and not stage 1, a similar predicate with the SOME keyword is not indexable and not stage 1.
  24. Under either of these circumstances, the predicate is stage 2:
    • noncol expr is a case expression.
    • noncol expr is the product or the quotient of two noncolumn expressions, that product or quotient is an integer value, and COL is a FLOAT or a DECIMAL column.
  25. COL IN (noncor subq) is stage 1 for type N access only. Otherwise, it is stage 2.
  26. If the inner table is an EBCDIC or ASCII column and the outer table is a Unicode column, the predicate is stage 1 and indexable.
  27. The XMLEXISTS is always stage 2. But the same predicate can be indexable and become the matching predicate if an XML index can be used to evaluate the XPath expression in the predicate. The XMLEXISTS predicate can never be a screening predicate.
  28. The predicate might be indexable by an expression-based index if it contains an expression that is a column reference, invokes a built-in function, or contains a general expression.
  29. This type of predicate is not stage 1 when a nullability mismatch is possible.
  30. Start of changeIf COL is defined with a field procedure, the predicate becomes stage 2.End of change
  31. Start of changeThe following predicates might be indexable and stage 1 if only the right side contains a DECFLOAT data type:End of change Start of change
    • COL = value
    • COL = noncol expr
    • COL op value
    • COL op noncol expr
    End of change Start of changeOther predicates that contain a DECFLOAT data type are not stage 1 and not indexable.End of change
  32. COL is a DATE, TIME, or TIMESTAMP column.

The following abbreviations and variable values are used in the preceding in the sample predicates:

char
Any character string that does not include the special characters for percent (%) or underscore (_).
COL
A column name.
col expr
A column expression.
cor subq
A correlated subquery
expression
Any expression that contains arithmetic operators, scalar functions, aggregate functions, concatenation operators, columns, constants, host variables, special registers, or date or time expressions.
noncol expr
A non-column expression, which is any expression that does not contain a column. That expression can contain arithmetic operators, scalar functions, concatenation operators, constants, host variables, special registers, or date or time expressions.

An example of a non-column expression is 

CURRENT DATE - 50 DAYS
noncor subq
A non-correlated subquery
op
any of the operators >, >=, <, <=, ¬>, ¬<
predicate
A predicate of any type.
pattern
Any character string that does not start with the special characters for percent (%) or underscore (_).
subq
A correlated or noncorrelated subquery
Tn
A table name.
Tn col expr
An expression that contains a column in table Tn. The expression might be only that column.
value
A constant, host variable, or special register.

End program-specific programming interface information.