Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Recursive subquery factoring using the SQL WITH clause

IT Tips by Burleson Consulting
September 6,  2009

Enhancements to the WITH clause

Starting in 11g R2, we see a new SQL enhancement, recursive subquery factoring using the SQL WITH clause.  First, review these notes to understand how powerful the WITH clause is for pre-aggregating and simplifying complex SQL queries:

To show how the WITH clause is used in ANSI SQL-99 syntax, the following is an excerpt from Jonathan Gennick' s article "Understanding the WITH Clause", showing the use of the SQL-99 WITH clause to traverse a recursive bill of materials hierarchy.  \
 
WITH recursiveBOM
   (assembly_id, assembly_name, parent_assembly) AS
(SELECT parent.assembly_id,
        parent.assembly_name,
        parent.parent_assembly
FROM bill_of_materials parent
WHERE parent.assembly_id=100
UNION ALL
SELECT child.assembly_id,
       child.assembly_name,
       child.parent_assembly
FROM recursiveBOM parent, bill_of_materials child
WHERE child.parent_assembly = parent.assembly_id)
SELECT assembly_id, parent_assembly, assembly_name
FROM recursiveBOM;

 
The WITH clause allows one to pre-materialize components of a complex query, making the entire query run faster.  This same technique can also be used with Global temporary tables.

The Oracle docs note: the syntax of the new WITH clause enhancement, targeted a hierarchical queries.

If a subquery_factoring_clause refers to its own query_name in the subquery that defines it, then the subquery_factoring_clause is said to be recursive. A recursive subquery_factoring_clause must contain two query blocks: the first is the anchor member and the second is the recursive member.

The anchor member must appear before the recursive member, and it cannot reference query_name. The anchor member can be composed of one or more query blocks combined by the set operators: union all, union, intersect or minus.

The recursive member must follow the anchor member and must reference query_name exactly once. You must combine the recursive member with the anchor member using the union all set operator.

This site shows an example of the 11g R2 new with clause syntax.

Here is a 11g R1 hierarchical query:

col text format a40
col mgr format 9999

select rpad(' ',2*(level-1))||empno||': '||ename text,
mgr
  from scott.emp
       connect by prior empno=mgr
       start with job='PRESIDENT'
 order siblings by ename;

TEXT                       MGR
----------------------------- -----
7839: KING
  7698: BLAKE                 7839
    7499: ALLEN               7698
    7900: JAMES               7698
    7654: MARTIN              7698
    7844: TURNER              7698
    7521: WARD                7698
  7782: CLARK                 7839
    7934: MILLER              7782
  7566: JONES                 7839
    7902: FORD                7566
      7369: SMITH             7902
    7788: SCOTT               7566
      7876: ADAMS             7788

Execution Plan
----------------------------------------------------------
Plan hash value: 763482334

-----------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    14 |   4    (25)|
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |      |
|   2 |   TABLE ACCESS FULL            | EMP  |    14 |   3     (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("MGR"=PRIOR "EMPNO")
       filter("JOB"='PRESIDENT')

And here, we see the equivalent query using the 11g R1 with clause recursive subquery factoring syntax:

col text format a40
col mgr format 9999

with empl (empno, ename, xlevel, mgr) as
  (select empno, ename, 1, mgr
from scott.emp
where job='PRESIDENT'
   union all
   select e.empno, e.ename, empl.xlevel+1, e.mgr
from scott.emp e, empl
where e.mgr=empl.empno)
  search depth first by ename set ord
select rpad(' ',2*xlevel)||empno||': '||ename text,
mgr
  from empl;

TEXT                       MGR
------------------------- -----
  7839: KING
    7698: BLAKE            7839
      7499: ALLEN           7698
      7900: JAMES           7698
      7654: MARTIN        7698
      7844: TURNER        7698
      7521: WARD           7698
    7782: CLARK            7839
      7934: MILLER        7782
    7566: JONES            7839
      7902: FORD           7566
    7369: SMITH            7902
      7788: SCOTT           7566
    7876: ADAMS            7788

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3907725112

-----------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |  8   (25)|
|   1 |  VIEW                    |      |    25 |    8   (25)|
|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|      |       |            |
|*  3 |    TABLE ACCESS FULL            | EMP  |     3 |    3    (0)|
|*  4 |    HASH JOIN                |      |    22 |    4   (25)|
|   5 |     RECURSIVE WITH PUMP         |      |       |       |
|*  6 |     TABLE ACCESS FULL            | EMP  |    13 |    3    (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='PRESIDENT')
   4 - access("E"."MGR"="EMPL"."EMPNO")
   6 - filter("E"."MGR" IS NOT NULL)

Recursive WITH clause

Oracle author Laurent Schneider has this great explanation of the Oracle connect by vs. recursive WITH clause performance, showing the "connect by" is 1% faster in a small subset:

CONNECT BY is an Oracle oddity. But does recursive with performs as well as connect by?

CONNECT BY Clause

select
   empno,mgr
from

   big_emp
connect by

   mgr = prior empno
start with

   mgr is null;

 

E M
- -
1 -  
2 1
4 2
6 2
8 6

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  185      78
CONNECT BY WITH FILTERING
TABLE ACCESS FULL         BIG_EMP      1    1   61      10
HASH JOIN                              2    2  122      46
CONNECT BY PUMP
TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000

Recursive WITH clause:

with
   e(empno,mgr) as (
     select
       empno,
       mgr
     from
       big_emp
     where
        mgr is null
     union all
     select

        f.empno,
        f.mgr
     from

         big_emp f, e
     where

       e.empno=f.mgr)
select

   empno,
   mgr
from e;

E M
- -
1 -  
2 1
3 1
4 2
5 3
...

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  183      78
VIEW                                   3    3  183      78
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL BIG_EMP              1    1   61      10
HASH JOIN                              2    2  122      46
RECURSIVE WITH PUMP
TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000

In this particular simple case, it seems CONNECT BY has a 1% higher cost.



 

 

??
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.