Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i


SQL> create table lsc_t as
2 select 1 parent, 2 child from dual
3 union all select 2,3 from dual
4 union all select 4,5 from dual
5 union all select 5,6 from dual
6 union all select 6,4 from dual;

Table created.

SQL> select parent,child
2 from lsc_t
3 where level=50
4 connect by parent=prior child
5 and level<=50 6 and prior dbms_random.value != dbms_random.value; PARENT CHILD ---------- ---------- 5 6 6 4 4 5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose :mrgreen:

10 thoughts on “Cycling

  1. Aketi Jyuuzou

    Wow this is a great solution.
    I have seen this DBMS_RANDOM usage in US-OTN-SQL-forum.
    However I did not realize that “Connect by nocycle” can be emulated.

    I have researched and tested that on Oracle9i.
    Then I derived version like below 😎

    http://oraclesqlpuzzle.hp.infoseek.co.jp/8-43.html
    Point is “and prior dbms_random.value != 1;” ,
    Because dbms_random.value returns 0 or greater then 0 and lower then 1

  2. Karl Reitschuster

    with SYS_GUID() you even have no PL/SQL context switch in your SQL – Karl

    SELECT PARENT,
    Child
    FROM Lsc_t
    WHERE LEVEL = 50
    CONNECT BY PARENT = PRIOR Child
    AND LEVEL <= 50 AND PRIOR sys_guid() != sys_guid();

  3. Laurent Schneider Post author

    well, in my humble opinion it is not supposed to work… It probably does not in oracle7 or 8 (anyone can check?)

    it is very similar to the connect by without prior row generator
    select * from dual connect by level<50

    we just trick Oracle to generate a loop and we limit the loop to 50 levels, in case the level reach 50, we consider it is a loop.

    it is the way new connnect by algorythm (introduced as a hidden parameter in 8174) detects loop

  4. David09

    Thank you very much for this solution. Can you please let me know how to detect cycling records without using 50 level restrictions? I have a table where I can have cycling records of more than 50 level

  5. Laurent Schneider Post author

    In Oracle 10g :

    select parent,child
    from lsc_t
    where connect_by_iscycle=1
    connect by nocycle parent=prior child
    ;

    In 9i, increase the 50 to whatever you feel relevant…

Comments are closed.