Compiler rewrite example: Predicate translation

During query rewrite, predicates can be added to a query to enable the optimizer to consider additional table joins when selecting the best access plan for the query.

The following query returns a list of the managers whose departments report to department 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

This query can be rewritten with the following implied predicate, known as a predicate for transitive closure:

   dept.mgrno = proj.respemp

The optimizer can now consider additional joins when it tries to select the best access plan for the query.

During the query rewrite stage, additional local predicates are derived on the basis of the transitivity that is implied by equality predicates. For example, the following query returns the names of the departments whose department number is greater than E00, and the employees who work in those departments.

   select empno, lastname, firstname, deptno, deptname
     from employee emp, department dept
     where
       emp.workdept = dept.deptno and
       dept.deptno > 'E00'

This query can be rewritten with the following implied predicate:

   emp.workdept > 'E00'

This rewrite reduces the number of rows that need to be joined.

Example - OR to IN transformations

Suppose that 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, using an IN predicate in place of OR causes the query to be processed more efficiently:

   select *
     from employee
     where deptno in ('D11', 'D21', 'E21')

In some cases, the database manager might convert an IN predicate into a set of OR clauses so that index ORing can be performed.