3

I thought I understand how Oracle's CONNECT BY works.

But then I found this.

select dual.*, CONNECT_BY_ISCYCLE from dual
connect by nocycle 1=1
and LEVEL <= 2
;

results in

DUMMY   CONNECT_BY_ISCYCLE
X       0
X       0

but 

select dual.*, CONNECT_BY_ISCYCLE from dual
connect by nocycle 1=1
and LEVEL <= 2
and prior dummy = dummy
;

produces only 1 row

DUMMY   CONNECT_BY_ISCYCLE
X       1

I expected same result as in the first query.

Can someone please explain or point me to an explanation of this?

This is on Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

Tymur Gubayev
  • 468
  • 4
  • 14

1 Answers1

4

How does Oracle identify "cycles" in a hierarchical query?

If it's documented, it's not done very well. Anyway, I think we found the correct and complete (and, really, pretty simple) answer in an OTN discussion: https://community.oracle.com/thread/3999985

Anyway: When you run a hierarchical query, for each row generated, Oracle will look at the columns that are operated upon by the PRIOR operator in the connect by clause, and ONLY to those columns, to see if a newly generated row matches an already existing one.

This is why "select 5 from dual connect by level <= 100" works, even though it generates the same row 100 times. There is no cycle, because "cycle" only looks at columns preceded by PRIOR in CONNECT BY - and there are none.

This is also why you will often see things like connect by [other conditions here] and prior sys_guid() is not null. sys_guid() will never be null; but by adding it to connect by, and very importantly with the PRIOR operator, you make sure there will never be cycles (because each call is guaranteed to return a different value!) so you are free to break "infinite recursion" by conditions on level or other means, but not by the identification of "cycles". (Due to this extra "column" to check, Oracle will never see a cycle.)

  • this is a very useful link, thanks. I wonder if it's documented somewhere (lack of documentation may indicate it's subject to an unannounced change). – Tymur Gubayev Jun 09 '17 at 12:25