Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How do you break the cycles in "connect by nocycles" query.

852711Apr 4 2011 — edited Apr 4 2011
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 2 2011
Added on Apr 4 2011
6 comments
1,442 views