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
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.
No comments:
Post a Comment