The SQL compiler will rewrite queries to move query operations in an attempt to construct the query with the minimum number of operations and predicates. The following examples are provided to illustrate some of the operations that can be moved by the SQL compiler:
During query rewrite, the optimizer can move where the DISTINCT operation is performed, to reduce the cost of this operation. In the example provided, the DISTINCT operation is removed completely.
During query rewrite, the order of applying predicates can be changed so that more selective predicates are applied to the query as early as possible.
When in a partitioned database environment the movement of results sets between database partitions is costly. Reducing the size of what must be broadcast to other database partitions and/or the number of broadcasts is one of the objectives when rewriting queries.
If the EMPNO column was defined as the primary key of the EMPLOYEE table, the following query:
SELECT DISTINCT EMPNO, FIRSTNME, LASTNAME
FROM EMPLOYEE
would be rewritten by removing the DISTINCT clause:
SELECT EMPNO, FIRSTNME, LASTNAME
FROM EMPLOYEE
In the above example, since the primary key is being selected, the SQL compiler knows that each row returned will already be unique. In this case, the DISTINCT key word is redundant. If the query was not rewritten, the optimizer would build a plan with the necessary processing (a sort, for example) to ensure that the columns are distinct.
Altering the level at which a predicate is normally applied can result in improved performance. For example, given the following view which provides a list of all employees in department "D11":
CREATE VIEW D11_EMPLOYEE
(EMPNO, FIRSTNME, LASTNAME, PHONENO, SALARY, BONUS, COMM)
AS SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO, SALARY, BONUS, COMM
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
And given the following query:
SELECT FIRSTNME, PHONENO
FROM D11_EMPLOYEE
WHERE LASTNAME = 'BROWN'
The query rewrite stage of the compiler will push the predicate LASTNAME = 'BROWN' down into the view D11_EMPLOYEE. This allows the predicate to be applied sooner and potentially more efficiently. The actual query that could be executed in this example is:
SELECT FIRSTNME, PHONENO
FROM EMPLOYEE
WHERE LASTNAME = 'BROWN'
AND WORKDEPT = 'D11'
Pushdown of predicates is not limited to views. Other situations in which predicates may be pushed down include UNIONs, GROUP BYs, and derived tables (nested table expressions or common table expressions).
In a partitioned database environment, the SQL compiler can rewrite the following query:
Find all the employees who are working on programming projects and are underpaid.
SELECT P.PROJNO, E.EMPNO, E.LASTNAME, E.FIRSTNAME,
E.SALARY+E.BONUS+E.COMM AS COMPENSATION
FROM EMPLOYEE E, PROJECT P
WHERE P.EMPNO = E.EMPNO
AND P.PROJNAME LIKE '%PROGRAMMING%'
AND E.SALARY+E.BONUS+E.COMM <
(SELECT AVG(E1.SALARY+E1.BONUS+E1.COMM)
FROM EMPLOYEE E1, PROJECT P1
WHERE P1.PROJNAME LIKE '%PROGRAMMING%'
AND P1.PROJNO = A.PROJNO
AND E1.EMPNO = P1.EMPNO)
Since this query is correlated, and since both PROJECT and EMPLOYEE are unlikely to be partitioned on PROJNO, the broadcast of each project to each database partition is possible. In addition, the subquery would have to be evaluated many times.
The SQL compiler can rewrite the query as follows:
WITH DIST_PROJS(PROJNO, EMPNO) AS
(SELECT DISTINCT PROJNO, EMPNO
FROM PROJECT P1
WHERE P1.PROJNAME LIKE '%PROGRAMMING%')
AVG_PER_PROJ(PROJNO, AVG_COMP) AS
(SELECT P2.PROJNO, AVG(E1.SALARY+E1.BONUS+E1.COMM)
FROM EMPLOYEE E1, DIST_PROJS P2
WHERE E1.EMPNO = P2.EMPNO
GROUP BY P2.PROJNO)
SELECT P.PROJNO, E.EMPNO, E.LASTNAME, E.FIRSTNAME,
E.SALARY+E.BONUS+E.COMM AS COMPENSATION
FROM PROJECT P, EMPLOYEE E, AVG_PER_PROG A
WHERE P.EMPNO = E.EMPNO
AND P.PROJNAME LIKE '%PROGRAMMING%'
AND P.PROJNO = A.PROJNO
AND E.SALARY+E.BONUS+E.COMM < A.AVG_COMP
The rewritten SQL query computes the AVG_COMP per project (AVG_PRE_PROJ) and can then broadcast the result to all database partitions containing the EMPLOYEE table.