How do you break the cycles in "connect by nocycles" query.
852711Apr 4 2011 — edited Apr 4 2011I looked at the documentation of the oracle hierarchical queries to see if there is a deterministic way of breaking the cycles.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/queries003.htm
I understand that plain connect by query fails if there are cycles in the data, this can be over come by using the nocycles clause. But how does oracle handle the cycles if it finds one, is there a deterministic way of breaking the cycles, or is random? If it is deterministic, Is there a sophisticated algorithm you use to break the cycles?
My sample query is as shown below:
SELECT ename "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(ename, ’/’) "Path"
FROM scott.emp
WHERE level <= 3 AND deptno = 10
START WITH ename = ’KING’
CONNECT BY NOCYCLE PRIOR empno = mgr AND LEVEL <= 4;
Thanks,
Paul