Debapriya Dutta Debapriya Dutta - 7 months ago 13
SQL Question

Why CONNECT_BY_ISCYCLE doesn't give 1 when table has a cycle?

As you all know

scott.emp
table of Oracle doesn't have any cycle in hierarchy.
But I changed
emp
table a little
(UPDATE emp SET mgr=7654 WHERE empno=7698)
where I changed Martin's manager as Blake and Blake's manager as Martin and fired following query.

SELECT
empno,
ename,
mgr,
level,
connect_by_iscycle
FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE prior empno=mgr
ORDER SIBLINGS BY mgr


But it doesn't give Martin and Blake related entries and doesn't display 1 at expected field. Please answer the reason if you can figure out.

Answer

Neither Martin nor Blake is ever reached in the hierarchy. You've made them mutually related, but no longer in any chain that you are selecting, because of the start condition.

The connection criteria is that prior emp = mgr so based on that they are related, but neither is now linked to anyone else above them. They form their own little island, and that island doesn't match the mgr IS NULL start condition. Or if you prefer, if you start from any record where mgr is null there is no path down to either of these records.

If you removed the start condition you would see a lot more data you don't want, but that would include Martin and Blake and would show the cycle flag for them.