Tags
How to implement CONNECT BY features
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 the CONNECT BY features using the new recursive WITH clause.
Cycle Detection
If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.
UPDATE emp
SET mgr = 7499
WHERE empno = 7839
/
SELECT LEVEL, e.ename
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.empno = 7839
ORDER SIBLINGS BY e.ename
/
ERROR:
ORA-01436: CONNECT BY loop in user data
Recursive suquery factoring throws a different error code with a similar message.
WITH r_emps (ename, empno, mgr, lvl, path, root)
AS (SELECT e.ename, e.empno, e.mgr
, 1 as lvl
, '/' || e.ename AS path
, e.ename AS root
FROM emp e
WHERE e.empno = 7839
UNION ALL
SELECT e.ename, e.empno, e.mgr
, m.lvl + 1 AS lvl
, m.path || '/' || e.ename AS path
, m.root
FROM emp e
INNER JOIN r_emps m ON (m.empno = e.mgr))
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename, e.empno, e.mgr, e.path, e.root
FROM r_emps e
ORDER BY e.sorting
/
ERROR at line ..:
ORA-32044: cycle detected while executing recursive WITH query
NOCYLCE
Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles and detect nodes with a connection to a node already included in the result set (cycle).
SELECT LEVEL
, CONNECT_BY_ISCYCLE AS CYCLE
, e.ename
, SYS_CONNECT_BY_PATH(e.ename, '/') AS path
, e.mgr
FROM emp e
CONNECT BY NOCYCLE PRIOR e.empno = e.mgr
START WITH e.empno = 7839
ORDER SIBLINGS BY e.ename
/
LEVEL CYCLE ENAME PATH MGR
----- ----- -------- --------------------- ----
1 0 KING /KING 7499
2 0 BLAKE /KING/BLAKE 7839
3 1 ALLEN /KING/BLAKE/ALLEN 7698
3 0 JAMES /KING/BLAKE/JAMES 7698
3 0 MARTIN /KING/BLAKE/MARTIN 7698
3 0 TURNER /KING/BLAKE/TURNER 7698
3 0 WARD /KING/BLAKE/WARD 7698
2 0 CLARK /KING/CLARK 7839
3 0 MILLER /KING/CLARK/MILLER 7782
2 0 JONES /KING/JONES 7839
3 0 FORD /KING/JONES/FORD 7566
4 0 SMITH /KING/JONES/FORD/SMITH 7902
3 0 SCOTT /KING/JONES/SCOTT 7566
The cycle was detected at node ALLAN (CONNECT_BY_ISCYCLE returned the value 1) and further processing of this branch is stopped.
The recursive WITH clause also offers a clause to handle cycles.
WITH r_emps (ename, empno, mgr, lvl, path)
AS (SELECT e.ename, e.empno, e.mgr, 1 AS lvl, '/' || e.ename AS path
FROM emp e
WHERE e.empno = 7839
UNION ALL
SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl, m.path || '/' || e.ename AS path
FROM r_emps m
INNER JOIN emp e ON (e.mgr = m.empno))
SEARCH DEPTH FIRST BY ename SET SORTING
CYCLE empno SET is_cycle TO 1 DEFAULT 0
SELECT e.lvl AS LEVEL, is_cycle AS CYCLE, e.ename, e.path ,e.mgr
FROM r_emps e
ORDER BY e.sorting
/
LEVEL CYCLE ENAME PATH MGR
----- ----- --------- ----------------------- ----
1 0 KING /KING 7499
2 0 BLAKE /KING/BLAKE 7839
3 0 ALLEN /KING/BLAKE/ALLEN 7698
4 1 KING /KING/BLAKE/ALLEN/KING 7499
3 0 JAMES /KING/BLAKE/JAMES 7698
3 0 MARTIN /KING/BLAKE/MARTIN 7698
3 0 TURNER /KING/BLAKE/TURNER 7698
3 0 WARD /KING/BLAKE/WARD 7698
2 0 CLARK /KING/CLARK 7839
3 0 MILLER /KING/CLARK/MILLER 7782
2 0 JONES /KING/JONES 7839
3 0 FORD /KING/JONES/FORD 7566
4 0 SMITH /KING/JONES/FORD/SMITH 7902
3 0 SCOTT /KING/JONES/SCOTT 7566
4 0 ADAMS /KING/JONES/SCOTT/ADAMS 7788
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.