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:
During query rewrite, predicates can be added to the query to allow the optimizer to consider additional table joins when selecting the best access plan for the query.
During query rewrite, an OR predicate can be translated into an IN predicate to allow for a more efficient access plan to be chosen. The SQL compiler can also translate an IN predicate into an OR predicate if this transformation would allow a more efficient access plan to be chosen.
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.
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. |