Oracle WITH Clause: Referencing Same Subquery Multiple Times

In complex queries that process the same subquery multiple times, is tempting to store the subquery results in a temporary table and perform additional queries against the temporary table. The WITH clause lets you factor out the subquery, give it a name, then reference that name multiple times within the original complex query.

This technique lets the optimizer choose how to deal with the subquery results -- whether to create a temporary table or inline it as a view.

For example, the following query joins two tables and computes the aggregate SUM(SAL) more than once. The bold text represents the parts of the query that are repeated.

Regular Subquery

----------------------------------------

    SELECT dname, SUM(sal) AS dept_total

     FROM emp, dept

      WHERE emp.deptno = dept.deptno

      GROUP BY dname HAVING

        SUM(sal) >

       (

          SELECT SUM(sal) * 1/3

            FROM emp, dept

           WHERE emp.deptno = dept.deptno

       )

       ORDER BY SUM(sal) DESC;

    DNAME          DEPT_TOTAL

    -------------- ----------

    RESEARCH            10875

    SALES                9400

    Execution Plan

    ------------------------------------------------------

       0      SELECT STATEMENT Optimizer=CHOOSE

       1    0   SORT (ORDER BY)

       2    1     FILTER

       3    2       SORT (GROUP BY)

       4    3         MERGE JOIN

       5    4           SORT (JOIN)

       6    5             TABLE ACCESS (FULL) OF 'DEPT'

       7    4           SORT (JOIN)

       8    7             TABLE ACCESS (FULL) OF 'EMP'

       9    2       SORT (AGGREGATE)

      10    9         MERGE JOIN

      11   10           SORT (JOIN)

      12   11             TABLE ACCESS (FULL) OF 'DEPT'

      13   10           SORT (JOIN)

      14   13             TABLE ACCESS (FULL) OF 'EMP'

You can improve the query by doing the subquery once, and referencing it at the appropriate points in the main query. The bold text represents the common parts of the subquery, and the places where the subquery is referenced.

    WITH

    summary AS

    (

     SELECT dname, SUM(sal) AS dept_total

     FROM emp, dept

     WHERE emp.deptno = dept.deptno

     GROUP BY dname

    )

    SELECT dname, dept_total

     FROM summary

      WHERE dept_total >

    (

      SELECT SUM(dept_total) * 1/3

     FROM summary

    )

    ORDER BY dept_total DESC;

    DNAME          DEPT_TOTAL

    -------------- ----------

    RESEARCH            10875

    SALES                9400

    Execution Plan

    ---------------------------------------------------

       0      SELECT STATEMENT Optimizer=CHOOSE

       1    2     RECURSIVE EXECUTION OF 'SYS_LE_2_0'

       2    0   TEMP TABLE TRANSFORMATION

       3    2     SORT (ORDER BY)

       4    3       FILTER

       5    4         VIEW

       6    5           TABLE ACCESS (FULL) OF 'SYS_TEMP'

       7    4         SORT (AGGREGATE)

       8    7           VIEW

       9    8             TABLE ACCESS (FULL) OF 'SYS_TEMP'