Skip to Main Content
  • Questions

  • cycle detected in recursive query where it seems to be no cycle

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tomáš.

Asked: September 16, 2020 - 12:29 pm UTC

Last updated: December 22, 2025 - 7:46 am UTC

Version: Oracle 11

Viewed 10K+ times! This question is

You Asked

I have recursive query on Oracle 11g table with undirected graph data. Each row represents one edge. The recursive query traverses all edges starting from given input edge. The idea of query is:

- input edge is at the 0th level
- for n>0, edge is on n-th level if it incides with node of some edge on (n-1)-th level.

Query:

with edges (id, a, b) as (
  select 1, 'X', 'Y' from dual union
  select 2, 'X', 'Y' from dual
), r (l, id, parent_a, parent_b, child_a, child_b, edge_seen) as (
  select 0, id, null, null, a, b, cast(collect(id) over () as sys.ku$_objnumset)
  from edges
  where id = 1 
  union all
  select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b
       , r.edge_seen multiset union distinct (cast(collect(e.id) over () as sys.ku$_objnumset))
  from r
  join edges e on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b))
    and e.id not member of (select r.edge_seen from dual)
)
select * from r;


The query worked well with other inputs until two parallel edges between same node pair occured. In this case, there is edge 1 on 0th level of recursion (initial row). I expected edge 2 would be added to result on 1st level of recursion since join condition holds. Instead I get "ORA-32044: cycle detected while executing recursive with query".

I know this error is reported when the row newly joined to recursive query result would be same as some existing row. What I don't understand is why Oracle treats row with same node ids but different edge id as duplicate. Adding

cycle child_a, child_b set iscycle to 1 default 0


clause gives iscycle=1 for new row, adding

cycle id, child_a, child_b set iscycle to 1 default 0


gives iscycle=0, which is both correct.


Is it some known Oracle 11g bug and what's the best way to handle it?

I cannot fill LiveSQL link form since LiveSQL supports only Oracle 19 and the problem is reproducible only in Oracle 11g which I can't migrate from. The dbfiddle equivalent is https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=43af3cfae920e31f9a2748c1c31b54ad .

Thanks.

and Chris said...

From the docs:

A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.

i.e. the database doesn't consider the whole row, only the values of the columns you use in the cycle clause.

So when you have only:

cycle child_a, child_b


The database does consider this a cycle because edge 2 has exactly the same A, B values as edge 1, which is its ancestor.

By adding edge_seen and the multiset union nonsense, what you've done is implement your own cycle clause!

You could remove this and cycle on ID alone:

with edges (id, a, b) as (
  select 1, 'X', 'Y' from dual union
  select 2, 'X', 'Y' from dual
), r (l, id, parent_a, parent_b, child_a, child_b) as (
  select 0, id, null, null, a, b
  from edges
  where id = 1 
  union all
  select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b
  from r
  join edges e on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b))
) 
  cycle id set iscycle to 1 default 0
select * from r
where  iscycle = 0;

L    ID    PARENT_A    PARENT_B    CHILD_A    CHILD_B    ISCYCLE   
   0     1 <null>      <null>      X          Y          0          
   1     2 X           Y           X          Y          0

Rating

  (4 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

unclarities about cycle detection and collection usage

Tomáš Záluský, March 04, 2021 - 10:24 pm UTC

Thanks for response, Chris.

I read definition of cycle you cited and I understand cycle clause makes it working. Actually I switched to cycle clause according to your advice.
However, I would like to understand the cause of ORA-32044 error when no cycle clause exists. In such case I assume the ancestor row is (0,1,null,null,X,Y,{1}) and the new row generated in recursive part is (1,2,X,Y,X,Y,{1,2}). What's the definition of cycle in case of absence of cycle clause? Or was the error caused by usage of collection?

And second unclarity: was the usage of collection wrong only because I mixed it with built-in cycle detection or aren't you recommending collections at all circumstances? I saw them in some of your tutorials. After replacing edge_seen collection by cycle clause I encountered performance problems having graph with 65 edges: while edge_seen keeps number of recursion levels as small as possible, traversal controlled by cycle clause generates has possible walks with lot of duplicities. Is there any way how to avoid it?
Chris Saxon
March 05, 2021 - 9:40 am UTC

What's the definition of cycle in case of absence of cycle clause?

From the docs:

a row forms a cycle if one of its ancestor rows has the same values for all the columns in the column alias list for query_name that are referenced in the WHERE clause of the recursive member.

In the example above, both rows have the same value for A and B and only these are in the join clause. As a result you have a cycle because both rows point to themselves as well as the other row. Cycle detection avoids this problem.

was the usage of collection wrong only because I mixed it with built-in cycle detection or aren't you recommending collections at all circumstances?

There are reasons for using collections, just not this!

In this case there's a much easier way to avoid revisiting the same point: check that the new ID > current ID:

with edges (id, a, b) as (
  select 1, 'X', 'Y' from dual union
  select 2, 'X', 'Y' from dual
), r (l, id, parent_a, parent_b, child_a, child_b) as (
  select 0, id, null, null, a, b
  from edges
  where id = 1 
  union all
  select r.l + 1, e.id, r.child_a, r.child_b, e.a, e.b
  from r
  join edges e 
  on (r.child_a in (e.a, e.b) or r.child_b in (e.a, e.b))
  and e.id > r.id
) 
select * from r;


It looks like you're solving a true graph traversal problem, in which case it's worth looking at the graph capabilities in the database to do this. These are now included in your database license, no extra costs!

https://blogs.oracle.com/oraclespatial/graph-database-and-analytics-for-everyone

Recursive CTE to simulate looping

Narendra, December 18, 2025 - 6:19 pm UTC

Hello Chris/Connor,

I am sure I am missing/misunderstanding something obvious but I am unable to figure out why below is not working.

Problem: I am trying to query attributes of a specific database session using only SQL at regular intervals. I can't create any objects with the access I have.
Oracle Database Version 19.28

As a first step, below appears to work as expected and produces output of 10 rows with about 10 seconds apart between first row and last row
with function f_delay(p_seconds NUMBER) 
return DATE
as
begin
  dbms_session.sleep(p_seconds);
  return sysdate ;
end;
src as (select 1 col, sysdate as as_on_dt from dual)
,recr (col, as_on_dt) as 
(
select col, as_on_dt from src
union all
select col + 1, f_delay(1)
from recr
where col < 10
)
select * from recr ;


However, when I try to apply the same approach to querying v$session, it fails with ORA-32044: cycle detected while executing recursive WITH query. My idea is to be able to query v$session for same session at regular interval, adding a COUNTER column that would increment for every row and hence would/should be different to avoid any cycle.

with function f_delay(p_seconds NUMBER) 
return DATE
as
begin
  dbms_session.sleep(p_seconds);
  return sysdate ;
end;
subq (sid, serial#, logon_time, status, last_call_et, as_on_dt, counter) as 
(
select sid, serial#, logon_time, status, last_call_et, sysdate as_on_dt, 1 as counter 
from v$session
where sid = sys_context('USERENV','SID')
union all
select a.sid, a.serial#, a.logon_time, a.status, a.last_call_et, f_delay(1), b.counter+1
from v$session a join subq b on a.sid = b.sid and a.serial# = b.serial#
where a.sid = sys_context('USERENV','SID')
and b.counter <= 10
)
select *
from subq ;


What am I missing? Is this approach even feasible?

p.s. I am aware that it might be easier to achieve looping using, say batch/shell/<any programming language> but I am curious whether the above approach can work.
I vaguely remember Tanel Poder coming up with his amazing session snapper script which loops over same metrics data using a logic that I am still trying to digest.

Thanks in advance
Connor McDonald
December 19, 2025 - 5:30 am UTC

Its a known issue with ANSI joins in recursive WITH. We rewrite ansi joins before execution, and as a result your recursive WITH becomes a nested query and it breaks.

Convert to old style join and you're good to go

SQL> with
  2  function f_delay(p_seconds NUMBER)
  3  return DATE
  4  as
  5  begin
  6    dbms_session.sleep(p_seconds);
  7    return sysdate ;
  8  end;
  9  subq (sid, serial#, logon_time, status, last_call_et, as_on_dt, counter) as
 10  (
 11  select sid, serial#, logon_time, status, last_call_et, sysdate as_on_dt, 1 as counter
 12  from v$session
 13  where sid = sys_context('USERENV','SID')
 14  union all
 15  select a.sid, a.serial#, a.logon_time, a.status, a.last_call_et, f_delay(0.2), b.counter+1
 16  from v$session a , subq b where a.sid = b.sid and a.serial# = b.serial#
 17  and a.sid = sys_context('USERENV','SID')
 18  and b.counter <= 10
 19  )
 20  select *
 21  from subq ;
 22  /

       SID    SERIAL# LOGON_TIME          STATUS   LAST_CALL_ET AS_ON_DT               COUNTER
---------- ---------- ------------------- -------- ------------ ------------------- ----------
      1328      20339 19/12/2025 13:24:45 ACTIVE              0 19/12/2025 13:29:09          1
      1328      20339 19/12/2025 13:24:45 ACTIVE              0 19/12/2025 13:29:09          2
      1328      20339 19/12/2025 13:24:45 ACTIVE              0 19/12/2025 13:29:09          3
      1328      20339 19/12/2025 13:24:45 ACTIVE              0 19/12/2025 13:29:09          4
      1328      20339 19/12/2025 13:24:45 ACTIVE              1 19/12/2025 13:29:10          5
      1328      20339 19/12/2025 13:24:45 ACTIVE              1 19/12/2025 13:29:10          6
      1328      20339 19/12/2025 13:24:45 ACTIVE              1 19/12/2025 13:29:10          7
      1328      20339 19/12/2025 13:24:45 ACTIVE              1 19/12/2025 13:29:10          8
      1328      20339 19/12/2025 13:24:45 ACTIVE              1 19/12/2025 13:29:10          9
      1328      20339 19/12/2025 13:24:45 ACTIVE              2 19/12/2025 13:29:11         10
      1328      20339 19/12/2025 13:24:45 ACTIVE              2 19/12/2025 13:29:11         11

11 rows selected.


Re: Recursive CTE to simulate looping

Narendra, December 19, 2025 - 9:21 am UTC

Hello Connor,

Awesome....thank you so much for helping with this. Was not aware of the ANSI join known issue and was almost certain that I was missing something obvious.
One minor question:

Would you know why this query output has first row duplicated, despite having delay function? Subsequent records follow the suite.

with function f_delay(p_seconds NUMBER)
return DATE
as
begin
  dbms_session.sleep(p_seconds);
  return sysdate ;
end;
subq (sid, serial#, logon_time, status, last_call_et, as_on_dt, counter) as
(
  select sid, serial#, logon_time, status, last_call_et, sysdate as_on_dt, 1 as counter
  from v$session
  where sid = sys_context('USERENV','SID')
  union all
  select a.sid, a.serial#, a.logon_time, a.status, a.last_call_et, f_delay(2), b.counter+1
  from v$session a , subq b where a.sid = b.sid and a.serial# = b.serial#
  and a.sid = sys_context('USERENV','SID')
  and b.counter <= 10
)
select *
from subq ;



160 32395 19-DEC-2025 08:16:45 ACTIVE 0 19-DEC-2025 09:19:47 1
160 32395 19-DEC-2025 08:16:45 ACTIVE 0 19-DEC-2025 09:19:49 2
160 32395 19-DEC-2025 08:16:45 ACTIVE 2 19-DEC-2025 09:19:51 3
160 32395 19-DEC-2025 08:16:45 ACTIVE 4 19-DEC-2025 09:19:53 4

Connor McDonald
December 22, 2025 - 7:46 am UTC

Duplicated?

You asked for 1 row in the first half of the union all, and 10 rows in the second.

That gives 11 rows no?

If you're referring to LAST_CALL_ET then

- first invocation (upper part of UNION ALL)
- second invocation (first loop of lower part)

both evaluate "a.last_call_et" at the same time


Default cycle detection in recursive queries

mathguy, December 20, 2025 - 2:00 am UTC

Interesting comments, Connor.

Its a known issue with ANSI joins in recursive WITH.

How long has this been known? I speculated about exactly this issue almost seven years ago, on what's now known as "Oracle Forums", in the SQL and PL/SQL space:

https://forums.oracle.com/ords/apexds/post/more-on-cycle-detection-in-recursive-factored-subqueries-9696

At least at that time, none of the forum participants seemed aware of the "known issue with ANSI joins in recursive WITH" - including a good number of very experienced Oracle users. I assumed it was a bug, but no one found a mention of it.

In any case, there doesn't seem to be any mention of this even in the documentation for version 26 (or did I miss it?)

We rewrite ansi joins before execution, and as a result your recursive WITH becomes a nested query and it breaks.

This seems to explain what I had found seven years ago. I understood WHAT was happening, but not WHY. If the recursive WITH is translated to comma-join syntax by making it a nested query, I see how the WHERE conditions and the join conditions can become separated and be treated differently. (In one of the replies in the thread from 2019, I speculated as to how this was happening - apparently that speculation was wrong.)

Are there other things known to break in this process? The only one I am aware of is this one about default CYCLE detection, and this one can be solved easily without resorting to old-style join.

Convert to old style join and you're good to go

I wouldn't make that recommendation; instead, I would suggest always using an explicit CYCLE clause. That way a future reader of the query (including its original writer!) won't have to guess, in addition to avoiding the problems discussed here.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.