Stage 1 and stage 2 predicates

Rows retrieved for a query go through two stages of processing. Certain predicates can be applied during the first stage of processing, whereas other cannot be applied until the second stage of processing. You can improve the performance of your queries by using predicates that can be applied during the first stage whenever possible.

Predicates that can be applied during the first stage of processing are called Stage 1 predicates. These predicates are also sometimes said to be sargable. Similarly, predicates that cannot be applied until the second stage of processing are called stage 2 predicates, and sometimes described as nonsargable or residual predicates.

Whether a predicate is stage 1 or stage 2 depends on the following factors:

• The syntax of the predicate.
• Data type and length of constants or columns in the predicate.

  A simple predicate whose syntax classifies it as indexable and stage 1 might not be indexable or stage 1 because of data types that are associated with the predicate. For example, a predicate that is associated with either columns or constants of the DECFLOAT data type is never treated as stage 1. Similarly a predicate that contains constants or columns whose lengths are too long also might not be stage 1 or indexable.

  For example, the following predicate is not indexable, where CHARCOL is defined as CHAR(6):

  CHARCOL > 'ABCDEFG'

  For example, The following predicate is not stage 1, If DECCOL is defined as DECIMAL(18,2), because the precision of the decimal column is greater than 15.:

  DECCOL > 34.5e0, 

• Whether DB2® evaluates the predicate before or after a join operation. A predicate that is evaluated after a join operation is always a stage 2 predicate.
• Join sequence.

  The same predicate might be stage 1 or stage 2, depending on the join sequence. Join sequence is the order in which DB2 joins tables when it evaluates a query. The join sequence is not necessarily the same as the order in which the tables appear in the predicate.

  For example, the predicate might be stage 1 or stage 2:

  T1.C1=T2.C1+1

  If T2 is the first table in the join sequence, the predicate is stage 1, but if T1 is the first table in the join sequence, the predicate is stage 2.

  You can determine the join sequence by executing EXPLAIN on the query and examining the resulting plan table.

All indexable predicates are stage 1. The predicate C1 LIKE %BC is stage 1, but is not indexable.