CONNECT BY NOCYCLE

the idea of this post was largely inspired by nuke_y on developpez.net (in French)

I will try to rephrase it in a hierarchical way.

Let’s take all managers of Adams in EMP :
SELECT empno, ename, mgr
FROM emp
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING

So far so good. Let’s imagine SCOTT has two managers, JONES and PAUL.

create table lsc_t AS
(SELECT ename, empno, mgr
FROM emp
UNION ALL
SELECT 'SCOTT', 7788, 9999
FROM DUAL
UNION ALL
SELECT 'PAUL', 9999, NULL
FROM DUAL);
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999
9999 PAUL

EMP is not supposed to allow this (primary key on empno), so I created a table LSC_T.

So far still fine, Paul is in my list of managers.

Let’s imagine Scott is the manager of Paul. In 9i and below, this would result in an ORA-0146 CONNECT BY loop in user data, but in 10g, and according to the doc The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.

Should I try ???

UPDATE lsc_t
SET mgr = 7788
WHERE ename = 'PAUL';
COMMIT ;
SELECT empno, ename, mgr
FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
EMPNO ENAME MGR
---------- ---------- ----------
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7788 SCOTT 9999

Due to a loop in user data, PAUL is no longer returned. I have no explanation so far.

9 thoughts on “CONNECT BY NOCYCLE”

  1. radino

    Minimalistic example:

    scott@orcl> select * from lsc_t;

    ENAME EMPNO MGR
    ---------- ---------- ----------
    ADAMS 7876 7788
    SCOTT 7788 9999
    PAUL 9999 7788

    scott@orcl> SELECT empno, ename, mgr, CONNECT_BY_ISCYCLE, level
    2 FROM lsc_t
    3 CONNECT BY NOCYCLE PRIOR mgr = empno
    4 START WITH ename = 'ADAMS' ;

    EMPNO ENAME MGR CONNECT_BY_ISCYCLE LEVEL
    ---------- ---------- ---------- ------------------ ----------
    7876 ADAMS 7788 0 1
    7788 SCOTT 9999 1 2

  3. Narendra

    That is a nice one. Not noticed till now.
    Is it because hierarchial query only generates rows (in a hierarchy) for which LEVEL can be determined? In this example, LEVEL for PAUL can not be determined as it can be 3 because it is parent of SCOTT (which is at LEVEL 2) but that contradicts with SCOTT being parent of PAUL.
    But then the inclusion of SCOTT can also be questioned for the same reason. The only reason for SCOTT being included appears to be in the START WITH clause. The START WITH clause (kind of) “filters” which hierarchies will be included in the result set. It seems the resultset is built (by oracle) incrementely with the records that will be at the bottom (or top?) of the hierarchy. In this case, since there is no cycling between ADAMS & SCOTT (START WITH ename = ‘ADAMS’; so ADAMS is at LEVEL 1; SCOTT is parent of ADAMS so SCOTT is at LEVEL 2), both are included in the output. However, when it attempts to process PAUL, it can not determine the LEVEL of PAUL and hence (probably) does not include the same in final output.
    Finally, if you omit the START WITH clause, all records are included in the output, with SCOTT being repeated at LEVEL 1 as well as 2 (as a result of cycling).

  4. Aketi Jyuuzou

    I have used 10g Enterprise Edition Release 10.2.0.1.0

    create table nocyTes(ID,nextID) as
    select 1,2 from dual union
    select 2,3 from dual union
    select 3,2 from dual union
    select 9,9 from dual;

    select ID,nextID
    from nocyTes
    start with id = 1
    connect by nocycle prior nextID = ID;

    ID nextID
    — ——
    1 2
    2 3

    I suppose above result is bug.
    Hehe I will add “prior RowID!= RowID” 😎

    select ID,nextID
    from nocyTes
    start with id = 1
    connect by nocycle prior nextID = ID
    and prior RowID!= RowID;

    ID NEXTID
    — ——
    1 2
    2 3
    3 2

    By the way,We can emulate hierarchicalSQL Using PL/SQL stack dataStructer :mrgreen:
    http://oraclesqlpuzzle.hp.infoseek.co.jp/plsql-5.html

  6. Narendra

    Aketi,

    Brilliant. I had tried ‘PRIOR dbms_random.rando is not null’ but it did not work so I thought maybe that workaround is no longer “supported”. But I guess it still works. Great!

  7. Laurent Schneider Post author

    Well, do not try this :
    WITH t AS
    (SELECT 1 x, 2 y
    FROM DUAL
    UNION ALL
    SELECT 2, 2
    FROM DUAL)
    SELECT * FROM t
    CONNECT BY NOCYCLE x = PRIOR y AND PRIOR SYS_GUID () IS NOT NULL
    START WITH x = 1;
    X Y
    - -
    1 2
    2 2
    2 2
    2 2
    2 2
    2 2
    2 2
    2 2
    2 2
    ...
    ORA-600 internal error [I told you not to try,0,0,0,0]

  8. Laurent Schneider Post author

    In case you have no ROWID, I suppose prior x!=x will be fine…
    WITH t AS
    (SELECT 1 x, 2 y
    FROM DUAL
    UNION ALL
    SELECT 2, 3
    FROM DUAL
    UNION ALL
    SELECT 3, 2
    FROM DUAL
    UNION ALL
    SELECT 2, 2
    FROM DUAL)
    SELECT x, y, CONNECT_BY_ISCYCLE, SYS_CONNECT_BY_PATH (x, '/') p
    FROM t
    CONNECT BY NOCYCLE x = PRIOR y AND x != PRIOR x
    START WITH x = 1;
    X Y CONNECT_BY_ISCYCLE P
    ---------- ---------- ------------------ --------------------
    1 2 0 /1
    2 3 0 /1/2
    3 2 1 /1/2/3
    2 2 0 /1/2/3/2
    2 2 0 /1/2

  9. Gary Coy

    What a beautiful website. So clean looking, nice sized monospace fonts. Awesome. Yes I came for the content. I’ll stay for the content and the styling 🙂

Comments are closed.