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.
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
dept.mgrno = proj.respemp
The
optimizer can now consider additional joins when it tries to select
the best access plan for the query. select empno, lastname, firstname, deptno, deptname
from employee emp, department dept
where
emp.workdept = dept.deptno and
dept.deptno > 'E00'
emp.workdept > 'E00'
This
rewrite reduces the number of rows that need to be joined. select *
from employee
where
deptno = 'D11' or
deptno = 'D21' or
deptno = 'E21'
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.