Predicates and access path selection

Predicates are found in the WHERE, HAVING, or ON clauses of SQL statements; they describe attributes of data.

Begin program-specific programming interface information.
The Predicates of a SQL statement affect how DB2® selects the access path for the statement. Because you can use SQL to express the same query in different ways, knowing how predicates affect path selection helps you write queries that access data efficiently.

Most predicates are based on the columns of a table. They either qualify rows (through an index) or reject rows (returned by a scan) when the table is accessed. The resulting qualified or rejected rows are independent of the access path that is chosen for that table.

The following query has three predicates: an equal predicate on C1, a BETWEEN predicate on C2, and a LIKE predicate on C3.

SELECT * FROM T1
  WHERE C1 = 10 AND
        C2 BETWEEN 10 AND 20 AND
        C3 NOT LIKE 'A%'

Predicates in a HAVING clause are not used when DB2 selects access paths. The term predicate herein refers only to predicates in WHERE or ON clauses. The following attributes of predicates influence access path selection:

  • The type of predicate, according to its operator or syntax.
  • Whether the predicate is indexable.
  • Whether the predicate is stage 1 or stage 2.
  • Whether the predicate contains a rowid column.
  • Whether the predicates in part of an ON clause.

The following terms are used to differentiate and classify certain kinds of predicates:

Simple or compound
A compound predicate is the result of two predicates, whether simple or compound, that are connected together by AND or OR Boolean operators. All others are simple.
Local or join
Local predicates reference only one table. They are local to the table and restrict the number of rows that are returned for that table. Join predicates involve more than one table or correlated reference. They determine the way rows are joined from two or more tables.
Boolean term
Any predicate that is not contained by a compound OR predicate structure is a Boolean term. If a Boolean term is evaluated false for a particular row, the whole WHERE clause is evaluated false for that row.

Predicates in the ON clause

The ON clause supplies the join condition in an outer join. For a full outer join, the clause can use only equal predicates. For other outer joins, the clause can use any predicates except predicates that contain subqueries.

Start of changeFor inner joins, ON clause predicates can supply the join condition and local filtering, and they are semantically equivalent to WHERE clause predicates.End of change

For full outer join, the ON clause is evaluated during the join operation like a stage 2 predicate.

In an outer join, predicates that are evaluated after the join are stage 2 predicates. Predicates in a table expression can be evaluated before the join and can therefore be stage 1 predicates.

For example, in the following statement, the predicate EDLEVEL > 100 is evaluated before the full join and is a stage 1 predicate:

SELECT * FROM (SELECT * FROM DSN8A10.EMP
   WHERE EDLEVEL  > 100) AS X FULL JOIN DSN8A10.DEPT
      ON X.WORKDEPT  = DSN8A10.DEPT.DEPTNO;

End program-specific programming interface information.