IBM Books

Administration Guide


Predicate Translation

The SQL compiler will rewrite queries to translate existing predicates to more optimal predicates for the specific query. The following examples are provided to illustrate some of the predicates that could be translated by the SQL compiler:

Example - Addition of Implied Predicates

The following query produces a list of the managers whose departments report to "E01" and the projects for which those managers are responsible:

   SELECT DEPT.DEPTNAME DEPT.MGRNO, EMP.LASTNAME, PROJ.PROJNAME
     FROM DEPARTMENT DEPT,
          EMPLOYEE   EMP,
          PROJECT    PROJ
    WHERE DEPT.ADMRDEPT = 'E01'
      AND DEPT.MGRNO = EMP.EMPNO
      AND EMP.EMPNO  = PROJ.RESPEMP

The query rewrite will add the following implied predicate:

   DEPT.MGRNO = PROJ.RESPEMP

As a result of this rewrite, the optimizer can consider additional joins when it is trying to select the best access plan for the query.

In addition to the above predicate transitive closure, query rewrite will also derive additional local predicates based on the transitivity implied by equality predicates. For example, the following query lists the names of the departments (whose department number is greater than "E00") and employees who work in that department.

   SELECT EMPNO, LASTNAME, FIRSTNAME, DEPTNO, DEPTNAME
     FROM EMPLOYEE EMP,
          DEPARTMENT DEPT
    WHERE EMP.WORKDEPT = DEPT.DEPTNO
      AND DEPT.DEPTNO > 'E00'

For this query, the rewrite stage will add the following implied predicate:

   EMP.WORKDEPT > 'E00'

As a result of this rewrite, the optimizer reduces the number of rows to be joined.

Example - OR to IN Transformations

Suppose an OR clause connects two or more simple equality predicates on the same column, as in the following example:

   SELECT *
     FROM EMPLOYEE
    WHERE DEPTNO = 'D11'
       OR DEPTNO = 'D21'
       OR DEPTNO = 'E21'

If there is no index on the DEPTNO column, converting the OR clause to the following IN predicate will allow the query to be processed more efficiently:

   SELECT *
     FROM EMPLOYEE
    WHERE DEPTNO IN ('D11', 'D21', 'E21')
Note:In some cases, the database manager may convert an IN predicate to a set of OR clauses so that index ORing may be performed. See Multiple Index Access for more information about index ORing.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]