Search code examples
sqloraclehierarchical-query

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.


Solution

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