Table expressions are frequently overlooked as a potential solution for resolving problems using only SQL. Table expressions can be used to force the optimizer to choose a specific processing order.
For example, consider the following query:
In this query, the detail rows that qualify from each table are joined prior to the GROUP BY processing. In general, there will be more EMP rows than DEPT rows because a department comprises multiple employees. Suppose there were 200 DEPT rows joined to 75,000 EMP rows. The join is done and then the GROUP BY is processed.
Instead, you can use table expressions to force the optimizer to process the aggregations on a table-by-table basis:
This will produce the same results but it should perform better.
In general, consider using table expressions to pre-filter FULL JOIN tables, to pre-filter null supplying tables of LEFT/RIGHT joins, to separate GROUP BY work, and to generate or derive data.
For example, consider the following query:
SELECT D.DEPTNO,
MIN(D.DEPTNAME) AS DEPT_NAME,
MIN(D.LOCATION) AS DEPT_LOCATION,
SUM(E.SALARY) AS TOTAL_SALARY
FROM DEPT D,
EMP E
WHERE D.DEPTNO = E.WORKDEPT
AND E.BONUS BETWEEN 0.00 AND 1000.00
GROUP BY D.DEPTNO;
In this query, the detail rows that qualify from each table are joined prior to the GROUP BY processing. In general, there will be more EMP rows than DEPT rows because a department comprises multiple employees. Suppose there were 200 DEPT rows joined to 75,000 EMP rows. The join is done and then the GROUP BY is processed.
Instead, you can use table expressions to force the optimizer to process the aggregations on a table-by-table basis:
SELECT D.DEPTNO,
D.DEPTNAME,
D.LOCATION,
E.TOTAL_SALARY
FROM DEPT D,
(SELECT WORKDEPT, SUM(SALARY) AS TOTAL_SALARY
FROM EMP E
WHERE E.BONUS BETWEEN 0.00 and 1000.00
GROUP BY E.WORKDEPT) AS E
WHERE D.DEPTNO = E.WORKDEPT;
This will produce the same results but it should perform better.
In general, consider using table expressions to pre-filter FULL JOIN tables, to pre-filter null supplying tables of LEFT/RIGHT joins, to separate GROUP BY work, and to generate or derive data.
