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.

8 thoughts on “CONNECT BY NOCYCLE”

  1. 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
    
    

  2. 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).

  3. 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” 8-)

    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

  4. 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!

  5. 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]

  6. 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                
    

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>