ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to
the well known CONNECT BY clause. This blog entry shows how to implement all CONNECT BY features in Recursive WITH clause.
CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):
rajesh@ORA11G> select empno,ename,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G> with r(empno,ename,mgr) as
2 ( select empno,ename,mgr
3 from emp
4 where mgr is null
5 union all
6 select e.empno,e.ename,r.empno
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7698 BLAKE 7839
7782 CLARK 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7788 SCOTT 7566
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
7369 SMITH 7902
7876 ADAMS 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.
The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.
rajesh@ORA11G> select level lvl, rpad(' ',2*level)||ename enames,empno,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 order siblings by ename;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result can be achieved using the recursive WITH clause with the SEARCH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(empno,ename,mgr,lvl) as
2 ( select empno,ename,mgr,1 as lvl
3 from emp
4 where mgr is null
5 union all
6 select e.empno, e.ename, e.mgr, lvl+1
7 from emp e, r
8 where e.mgr = r.empno
9 )
10 search depth first by ename set ord1
11 select lvl, rpad(' ',2*lvl)||ename as enames, empno,mgr
12 from r
13 order by ord1;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.02
rajesh@ORA11G>
SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children.
CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy.
rajesh@ORA11G> select ename, empno, mgr,
2 sys_connect_by_path(ename,'/') path,
3 connect_by_root ename
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr ;
ENAME EMPNO MGR PATH CONNECT_BY
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
FORD 7902 7566 /KING/JONES/FORD KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
BLAKE 7698 7839 /KING/BLAKE KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
CLARK 7782 7839 /KING/CLARK KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(ename,empno,mgr,path,by_root) as
2 ( select ename,empno,mgr,
3 '/'||ename as path,
4 ename as by_root
5 from emp
6 where mgr is null
7 union all
8 select e.ename, e.empno, e.mgr,
9 r.path||'/'||e.ename as path,
10 r.by_root
11 from emp e, r
12 where e.mgr = r.empno )
13 select ename,empno,mgr,path,by_root from r ;
ENAME EMPNO MGR PATH BY_ROOT
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
BLAKE 7698 7839 /KING/BLAKE KING
CLARK 7782 7839 /KING/CLARK KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
FORD 7902 7566 /KING/JONES/FORD KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
14 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.
rajesh@ORA11G>
rajesh@ORA11G> UPDATE emp
2 SET mgr = 7499
3 WHERE empno = 7839 ;
1 row updated.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> select level, empno,ename
2 from emp
3 start with empno = 7839
4 connect by prior empno = mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Recursive suquery factoring throws a different error code with a similar message
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename) as
2 ( select 1 as lvl, empno, ename
3 from emp
4 where empno = 7839
5 union all
6 select lvl+1, e.empno, e.ename
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r ;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles
rajesh@ORA11G> select level, empno,ename,mgr,connect_by_iscycle,
2 sys_connect_by_path(ename,'/') path
3 from emp
4 start with empno = 7839
5 connect by nocycle prior empno = mgr;
LEVEL EMPNO ENAME MGR CONNECT_BY_ISCYCLE PATH
---------- ---------- ---------- ---------- ------------------ -----------------------------
1 7839 KING 7499 0 /KING
2 7566 JONES 7839 0 /KING/JONES
3 7788 SCOTT 7566 0 /KING/JONES/SCOTT
4 7876 ADAMS 7788 0 /KING/JONES/SCOTT/ADAMS
3 7902 FORD 7566 0 /KING/JONES/FORD
4 7369 SMITH 7902 0 /KING/JONES/FORD/SMITH
2 7698 BLAKE 7839 0 /KING/BLAKE
3 7499 ALLEN 7698 1 /KING/BLAKE/ALLEN
3 7521 WARD 7698 0 /KING/BLAKE/WARD
3 7654 MARTIN 7698 0 /KING/BLAKE/MARTIN
3 7844 TURNER 7698 0 /KING/BLAKE/TURNER
3 7900 JAMES 7698 0 /KING/BLAKE/JAMES
2 7782 CLARK 7839 0 /KING/CLARK
3 7934 MILLER 7782 0 /KING/CLARK/MILLER
14 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
The Recursive WITH clause also offers clause to handle this way.
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename,mgr,path) as
2 ( select 1 as lvl, empno, ename,
3 mgr,'/'||ename as path
4 from emp
5 where empno = 7839
6 union all
7 select lvl+1, e.empno, e.ename,
8 e.mgr,r.path||'/'||e.ename
9 from emp e, r
10 where e.mgr = r.empno )
11 search depth first by ename set ord1
12 cycle empno set y_cycle to 1 default 0
13 select lvl,empno,ename,mgr,path,y_cycle from r ;
LVL EMPNO ENAME MGR PATH Y
---------- ---------- ---------- ---------- ------------------------------ -
1 7839 KING 7499 /KING 0
2 7698 BLAKE 7839 /KING/BLAKE 0
3 7499 ALLEN 7698 /KING/BLAKE/ALLEN 0
4 7839 KING 7499 /KING/BLAKE/ALLEN/KING 1
3 7900 JAMES 7698 /KING/BLAKE/JAMES 0
3 7654 MARTIN 7698 /KING/BLAKE/MARTIN 0
3 7844 TURNER 7698 /KING/BLAKE/TURNER 0
3 7521 WARD 7698 /KING/BLAKE/WARD 0
2 7782 CLARK 7839 /KING/CLARK 0
3 7934 MILLER 7782 /KING/CLARK/MILLER 0
2 7566 JONES 7839 /KING/JONES 0
3 7902 FORD 7566 /KING/JONES/FORD 0
4 7369 SMITH 7902 /KING/JONES/FORD/SMITH 0
3 7788 SCOTT 7566 /KING/JONES/SCOTT 0
4 7876 ADAMS 7788 /KING/JONES/SCOTT/ADAMS 0
15 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):
rajesh@ORA11G> select empno,ename,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G> with r(empno,ename,mgr) as
2 ( select empno,ename,mgr
3 from emp
4 where mgr is null
5 union all
6 select e.empno,e.ename,r.empno
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7698 BLAKE 7839
7782 CLARK 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7788 SCOTT 7566
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
7369 SMITH 7902
7876 ADAMS 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.
The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.
rajesh@ORA11G> select level lvl, rpad(' ',2*level)||ename enames,empno,mgr
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 order siblings by ename;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result can be achieved using the recursive WITH clause with the SEARCH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(empno,ename,mgr,lvl) as
2 ( select empno,ename,mgr,1 as lvl
3 from emp
4 where mgr is null
5 union all
6 select e.empno, e.ename, e.mgr, lvl+1
7 from emp e, r
8 where e.mgr = r.empno
9 )
10 search depth first by ename set ord1
11 select lvl, rpad(' ',2*lvl)||ename as enames, empno,mgr
12 from r
13 order by ord1;
LVL ENAMES EMPNO MGR
---------- -------------------- ---------- ----------
1 KING 7839
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 JAMES 7900 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 WARD 7521 7698
2 CLARK 7782 7839
3 MILLER 7934 7782
2 JONES 7566 7839
3 FORD 7902 7566
4 SMITH 7369 7902
3 SCOTT 7788 7566
4 ADAMS 7876 7788
14 rows selected.
Elapsed: 00:00:00.02
rajesh@ORA11G>
SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children.
CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy.
rajesh@ORA11G> select ename, empno, mgr,
2 sys_connect_by_path(ename,'/') path,
3 connect_by_root ename
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr ;
ENAME EMPNO MGR PATH CONNECT_BY
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
FORD 7902 7566 /KING/JONES/FORD KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
BLAKE 7698 7839 /KING/BLAKE KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
CLARK 7782 7839 /KING/CLARK KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
14 rows selected.
Elapsed: 00:00:00.01
rajesh@ORA11G>
Same result using the recursive WITH clause
rajesh@ORA11G>
rajesh@ORA11G> with r(ename,empno,mgr,path,by_root) as
2 ( select ename,empno,mgr,
3 '/'||ename as path,
4 ename as by_root
5 from emp
6 where mgr is null
7 union all
8 select e.ename, e.empno, e.mgr,
9 r.path||'/'||e.ename as path,
10 r.by_root
11 from emp e, r
12 where e.mgr = r.empno )
13 select ename,empno,mgr,path,by_root from r ;
ENAME EMPNO MGR PATH BY_ROOT
---------- ---------- ---------- ------------------------------ ----------
KING 7839 /KING KING
JONES 7566 7839 /KING/JONES KING
BLAKE 7698 7839 /KING/BLAKE KING
CLARK 7782 7839 /KING/CLARK KING
ALLEN 7499 7698 /KING/BLAKE/ALLEN KING
WARD 7521 7698 /KING/BLAKE/WARD KING
MARTIN 7654 7698 /KING/BLAKE/MARTIN KING
SCOTT 7788 7566 /KING/JONES/SCOTT KING
TURNER 7844 7698 /KING/BLAKE/TURNER KING
JAMES 7900 7698 /KING/BLAKE/JAMES KING
FORD 7902 7566 /KING/JONES/FORD KING
MILLER 7934 7782 /KING/CLARK/MILLER KING
SMITH 7369 7902 /KING/JONES/FORD/SMITH KING
ADAMS 7876 7788 /KING/JONES/SCOTT/ADAMS KING
14 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.
rajesh@ORA11G>
rajesh@ORA11G> UPDATE emp
2 SET mgr = 7499
3 WHERE empno = 7839 ;
1 row updated.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> select level, empno,ename
2 from emp
3 start with empno = 7839
4 connect by prior empno = mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Recursive suquery factoring throws a different error code with a similar message
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename) as
2 ( select 1 as lvl, empno, ename
3 from emp
4 where empno = 7839
5 union all
6 select lvl+1, e.empno, e.ename
7 from emp e, r
8 where e.mgr = r.empno )
9 select * from r ;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA11G>
Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles
rajesh@ORA11G> select level, empno,ename,mgr,connect_by_iscycle,
2 sys_connect_by_path(ename,'/') path
3 from emp
4 start with empno = 7839
5 connect by nocycle prior empno = mgr;
LEVEL EMPNO ENAME MGR CONNECT_BY_ISCYCLE PATH
---------- ---------- ---------- ---------- ------------------ -----------------------------
1 7839 KING 7499 0 /KING
2 7566 JONES 7839 0 /KING/JONES
3 7788 SCOTT 7566 0 /KING/JONES/SCOTT
4 7876 ADAMS 7788 0 /KING/JONES/SCOTT/ADAMS
3 7902 FORD 7566 0 /KING/JONES/FORD
4 7369 SMITH 7902 0 /KING/JONES/FORD/SMITH
2 7698 BLAKE 7839 0 /KING/BLAKE
3 7499 ALLEN 7698 1 /KING/BLAKE/ALLEN
3 7521 WARD 7698 0 /KING/BLAKE/WARD
3 7654 MARTIN 7698 0 /KING/BLAKE/MARTIN
3 7844 TURNER 7698 0 /KING/BLAKE/TURNER
3 7900 JAMES 7698 0 /KING/BLAKE/JAMES
2 7782 CLARK 7839 0 /KING/CLARK
3 7934 MILLER 7782 0 /KING/CLARK/MILLER
14 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
The Recursive WITH clause also offers clause to handle this way.
rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename,mgr,path) as
2 ( select 1 as lvl, empno, ename,
3 mgr,'/'||ename as path
4 from emp
5 where empno = 7839
6 union all
7 select lvl+1, e.empno, e.ename,
8 e.mgr,r.path||'/'||e.ename
9 from emp e, r
10 where e.mgr = r.empno )
11 search depth first by ename set ord1
12 cycle empno set y_cycle to 1 default 0
13 select lvl,empno,ename,mgr,path,y_cycle from r ;
LVL EMPNO ENAME MGR PATH Y
---------- ---------- ---------- ---------- ------------------------------ -
1 7839 KING 7499 /KING 0
2 7698 BLAKE 7839 /KING/BLAKE 0
3 7499 ALLEN 7698 /KING/BLAKE/ALLEN 0
4 7839 KING 7499 /KING/BLAKE/ALLEN/KING 1
3 7900 JAMES 7698 /KING/BLAKE/JAMES 0
3 7654 MARTIN 7698 /KING/BLAKE/MARTIN 0
3 7844 TURNER 7698 /KING/BLAKE/TURNER 0
3 7521 WARD 7698 /KING/BLAKE/WARD 0
2 7782 CLARK 7839 /KING/CLARK 0
3 7934 MILLER 7782 /KING/CLARK/MILLER 0
2 7566 JONES 7839 /KING/JONES 0
3 7902 FORD 7566 /KING/JONES/FORD 0
4 7369 SMITH 7902 /KING/JONES/FORD/SMITH 0
3 7788 SCOTT 7566 /KING/JONES/SCOTT 0
4 7876 ADAMS 7788 /KING/JONES/SCOTT/ADAMS 0
15 rows selected.
Elapsed: 00:00:00.03
rajesh@ORA11G>
The difference between CONNECT BY and RECURSIVE WITH cycle
detection is that with RECURSIVE WITH the cycle is decteted after the next
recursion level wass processed. The erroneous node is repeated and the cycle
flag is set one level lower than the CONNECT_BY_ISCYCLE pseudo column.
