Subquery Factoring in Oracle 11g

For the last two days the AMIS 7Up Workshop took place in our office in Nieuwegein. The training consists of two SQL and another two days of PL/SQL. During the first two days a lot of SQL subjects are reviewed.

One of them is Subquery Factoring, better known as the “WITH clause”. Subquery Factoring lets you assign a name to a subquery block. Let me show you a small example:

SQL> with ordered_emps as
  2  (select ename
  3        , job
  4        , sal
  5        , deptno
  6     from emp
  7    order by ename
  8  )
  9  select *
 10    from ordered_emps
 11  ;

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
ADAMS      CLERK           1100         20
ALLEN      SALESMAN        1600         30
BLAKE      MANAGER         2850         30
CLARK      MANAGER         2450         10
FORD       ANALYST         3000         20
JAMES      CLERK            950         30
JONES      MANAGER         2975         20
KING       PRESIDENT       5000         10
MARTIN     SALESMAN        1250         30
MILLER     CLERK           1300         10
SCOTT      ANALYST         3000         20
SMITH      CLERK            800         20
TURNER     SALESMAN        1500         30
WARD       SALESMAN        1250         30

14 rows selected.

I know this is a contrived example, but it shows you what Subquery Factoring can do. What really adds p’zazz to this feature is the possibility to add multiple named subquery blocks, even using the subquery previously defined, like this:

SQL> with ordered_emps as
  2  (select ename
  3        , job
  4        , sal
  5        , deptno
  6     from emp
  7    order by ename
  8  )
  9  , top3 as
 10  (select *
 11     from ordered_emps
 12    where rownum <= 3
 13  )
 14  select *
 15    from top3
 16  ;

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
ADAMS      CLERK           1100         20
ALLEN      SALESMAN        1600         30
BLAKE      MANAGER         2850         30

In the example above, you can see that the named query “top3” uses the previously named query “ordered_emps”. It’s almost like adding procedural logic to a SQL statement.

Things keep changing,… for the better

The catch, at least prior to Oracle 11g, was that the named query must be used in the SQL statement. Wether in another named query (like above) or in the main SELECT statement (like the first example).

If you would declare a named query and not use it, you would get an exception:

SQL> with ordered_emps as
  2  (select ename
  3        , job
  4        , sal
  5        , deptno
  6     from emp
  7    order by ename
  8  )
  9  , top3 as
 10  (select *
 11     from ordered_emps
 12    where rownum <= 3
 13  )
 14  select *
 15    from ordered_emps
 16  /

  from ordered_emps
       *
ERROR at line 15:
ORA-32035: unreferenced query name defined in WITH clause

We did specify “Top3” as a named query but never use it in the SQL statement. And this is what I have been telling during the 7Up training all along.

Needless to say something changed in Oracle 11g. This requirement is no longer present.  I couldn’t find it in the documentation though. Probably because I’m looking in the wrong place…

SQL> select *
  2    from v$version
  3  /


BANNER
------------------------------------------------------------
Personal Oracle Database 11g Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> with ordered_emps as
  2  (select ename
  3        , job
  4        , sal
  5        , deptno
  6     from emp
  7    order by ename
  8  )
  9  , top3 as
 10  (select *
 11     from ordered_emps
 12    where rownum <= 3
 13  )
 14  select *
 15    from ordered_emps
 16  /

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
ADAMS      CLERK           1100         20
ALLEN      SALESMAN        1600         30
BLAKE      MANAGER         2850         30
CLARK      MANAGER         2450         10
FORD       ANALYST         3000         20
JAMES      CLERK            950         30
JONES      MANAGER         2975         20
KING       PRESIDENT       5000         10
MARTIN     SALESMAN        1250         30
MILLER     CLERK           1300         10
SCOTT      ANALYST         3000         20
SMITH      CLERK            800         20
TURNER     SALESMAN        1500         30
WARD       SALESMAN        1250         30

14 rows selected.

As you can see, the “Top3” named query is not used, and there is no exception.

Thanks

Thanks to Suzanne and Kaj for discovering this little gem. See you both week next for the second part of the 7Up workshop!

Documentation

Oracle 11g on Subquery Factoring

Oracle 10g on Subquery Factoring