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.
and include the URL for the page.
