What exactly does Oracle NOCYCLE parameter do in hierarchical queries?

Question

Years ago Oracle introduced an optional NOCYCLE parameter to hierarchical queries. However, there is little clarity as to what exactly it does, besides avoiding throwing an error when a cycle is detected. Most of the material that I have seen on the web is wrong or misleading. For example, the authoritative Oracle-Base says:

CONNECT BY NOCYCLE clause tells the database not to traverse cyclical hierarchies. (Wrong!)

The official documentation is, perhaps intentionally, noncommittal:

The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.

True enough, but this doesn't say when or even whether the query will stop following a loop when it runs into one.

The truth is that in some cases, when traversing a cyclical graph with CONNECT BY NOCYCLE Oracle will enter loops, but stop just short of completing them, returning a terminating response. In other cases, however, Oracle will happily run in circles forever. (Indeed, the above Oracle article mentions cases when an infinite loop may occur, albeit not in the same context.)

Is there a prescribed way for a hierarchical query with NOCYCLE clause to behave with respect to cycles?

ETA: Here is a working example of an apparently infinite loop that you can try in http://sqlfiddle.com, borrowed from this post. (I am not providing a permalink, because the script somehow becomes corrupted after it is run once, so you have to start a new one each time.)

Setup:

CREATE TABLE edges (tail CHAR(1), head CHAR(1));

INSERT INTO edges values('A','B');
INSERT INTO edges values('A','C');
INSERT INTO edges values('A','D');
INSERT INTO edges values('A','E');
INSERT INTO edges values('A','F');
INSERT INTO edges values('A','G');
INSERT INTO edges values('A','H');
INSERT INTO edges values('A','I');
INSERT INTO edges values('A','J');
INSERT INTO edges values('B','K');
INSERT INTO edges values('B','L');
INSERT INTO edges values('B','C');
INSERT INTO edges values('B','E');
INSERT INTO edges values('B','M');
INSERT INTO edges values('B','N');
INSERT INTO edges values('B','O');
INSERT INTO edges values('B','J');
INSERT INTO edges values('C','K');
INSERT INTO edges values('C','N');
INSERT INTO edges values('C','J');
INSERT INTO edges values('D','K');
INSERT INTO edges values('D','B');
INSERT INTO edges values('D','L');
INSERT INTO edges values('D','C');
INSERT INTO edges values('D','E');
INSERT INTO edges values('D','F');
INSERT INTO edges values('D','G');
INSERT INTO edges values('D','M');
INSERT INTO edges values('D','P');
INSERT INTO edges values('D','Q');
INSERT INTO edges values('D','N');
INSERT INTO edges values('D','R');
INSERT INTO edges values('D','O');
INSERT INTO edges values('D','J');
INSERT INTO edges values('E','K');
INSERT INTO edges values('E','L');
INSERT INTO edges values('E','S');
INSERT INTO edges values('E','M');
INSERT INTO edges values('E','N');
INSERT INTO edges values('E','O');
INSERT INTO edges values('E','J');
INSERT INTO edges values('F','K');
INSERT INTO edges values('F','B');
INSERT INTO edges values('F','L');
INSERT INTO edges values('F','C');
INSERT INTO edges values('F','G');
INSERT INTO edges values('F','S');
INSERT INTO edges values('F','M');
INSERT INTO edges values('F','N');
INSERT INTO edges values('E','O');
INSERT INTO edges values('E','J');
INSERT INTO edges values('G','K');
INSERT INTO edges values('G','B');
INSERT INTO edges values('G','L');
INSERT INTO edges values('G','E');
INSERT INTO edges values('G','M');
INSERT INTO edges values('G','N');
INSERT INTO edges values('G','O');
INSERT INTO edges values('G','J');
INSERT INTO edges values('H','K');
INSERT INTO edges values('H','B');
INSERT INTO edges values('H','L');
INSERT INTO edges values('H','C');
INSERT INTO edges values('H','D');
INSERT INTO edges values('H','E');
INSERT INTO edges values('H','F');
INSERT INTO edges values('H','G');
INSERT INTO edges values('H','M');
INSERT INTO edges values('H','P');
INSERT INTO edges values('H','Q');
INSERT INTO edges values('H','R');
INSERT INTO edges values('H','O');
INSERT INTO edges values('H','J');
INSERT INTO edges values('I','K');
INSERT INTO edges values('I','B');
INSERT INTO edges values('I','L');
INSERT INTO edges values('I','C');
INSERT INTO edges values('I','D');
INSERT INTO edges values('I','E');
INSERT INTO edges values('I','F');
INSERT INTO edges values('I','G');
INSERT INTO edges values('I','H');
INSERT INTO edges values('I','M');
INSERT INTO edges values('I','P');
INSERT INTO edges values('I','Q');
INSERT INTO edges values('I','N');
INSERT INTO edges values('I','R');
INSERT INTO edges values('I','O');
INSERT INTO edges values('I','J');
INSERT INTO edges values('J','K');
INSERT INTO edges values('J','L');
INSERT INTO edges values('J','N');
INSERT INTO edges values('J','O');
INSERT INTO edges values('K','N');
INSERT INTO edges values('K','J');
INSERT INTO edges values('L','K');
INSERT INTO edges values('L','N');
INSERT INTO edges values('L','J');
INSERT INTO edges values('M','K');
INSERT INTO edges values('M','B');
INSERT INTO edges values('M','L');
INSERT INTO edges values('M','C');
INSERT INTO edges values('M','E');
INSERT INTO edges values('M','F');
INSERT INTO edges values('M','G');
INSERT INTO edges values('M','N');
INSERT INTO edges values('M','O');
INSERT INTO edges values('M','J');
INSERT INTO edges values('M','F');
INSERT INTO edges values('N','J');
INSERT INTO edges values('O','K');
INSERT INTO edges values('O','L');
INSERT INTO edges values('O','N');
INSERT INTO edges values('O','J');
INSERT INTO edges values('P','K');
INSERT INTO edges values('P','A');
INSERT INTO edges values('P','B');
INSERT INTO edges values('P','L');
INSERT INTO edges values('P','T');
INSERT INTO edges values('P','C');
INSERT INTO edges values('P','D');
INSERT INTO edges values('P','E');
INSERT INTO edges values('P','F');
INSERT INTO edges values('P','G');
INSERT INTO edges values('P','H');
INSERT INTO edges values('P','I');
INSERT INTO edges values('P','N');
INSERT INTO edges values('P','R');
INSERT INTO edges values('P','O');
INSERT INTO edges values('P','J');
INSERT INTO edges values('Q','H');
INSERT INTO edges values('Q','I');
INSERT INTO edges values('Q','D');
INSERT INTO edges values('Q','E');
INSERT INTO edges values('Q','F');
INSERT INTO edges values('Q','G');
INSERT INTO edges values('Q','N');
INSERT INTO edges values('Q','L');
INSERT INTO edges values('Q','K');
INSERT INTO edges values('Q','R');
INSERT INTO edges values('Q','T');
INSERT INTO edges values('Q','A');
INSERT INTO edges values('Q','C');
INSERT INTO edges values('Q','B');
INSERT INTO edges values('Q','O');
INSERT INTO edges values('Q','J');
INSERT INTO edges values('R','K');
INSERT INTO edges values('R','B');
INSERT INTO edges values('R','L');
INSERT INTO edges values('R','C');
INSERT INTO edges values('R','G');
INSERT INTO edges values('R','M');
INSERT INTO edges values('R','N');
INSERT INTO edges values('R','O');
INSERT INTO edges values('R','J');
INSERT INTO edges values('S','K');
INSERT INTO edges values('S','B');
INSERT INTO edges values('S','L');
INSERT INTO edges values('S','C');
INSERT INTO edges values('S','E');
INSERT INTO edges values('S','F');
INSERT INTO edges values('S','G');
INSERT INTO edges values('S','N');
INSERT INTO edges values('S','O');
INSERT INTO edges values('S','J');
INSERT INTO edges values('T','K');
INSERT INTO edges values('T','A');
INSERT INTO edges values('T','B');
INSERT INTO edges values('T','L');
INSERT INTO edges values('T','C');
INSERT INTO edges values('T','D');
INSERT INTO edges values('T','E');
INSERT INTO edges values('T','F');
INSERT INTO edges values('T','G');
INSERT INTO edges values('T','H');
INSERT INTO edges values('T','I');
INSERT INTO edges values('T','M');
INSERT INTO edges values('T','P');
INSERT INTO edges values('T','Q');
INSERT INTO edges values('T','R');
INSERT INTO edges values('T','O');
INSERT INTO edges values('T','J');

Script:

select tail, head 
from edges
connect by nocycle
   prior head = tail
start with head = 'A';

Are you sure that the data above causes infinite loop and not just millions of possible paths? — astentx, May 21 '23 at 21:34
@astentx Good point. No, I am not sure. However, on the assumption that the query does not loop, this should not have been the case. You can't see this in sqlfiddle, but with a client that returns intermediate results, you can see that in more complex cases like this the query does loop, while in other cases (such as those that are often used in tutorials) it doesn't. Hence again my question: what, if anything, is it **supposed** to do? — Dmitry E., May 22 '23 at 08:12
Can you post an MRE or highlight in your example specifically an example of where you feel the behaviour is ambiguous/incorrect? I feel like this may come down to how Oracle defines a cycle, possibly that revisiting the same 'row' is not equivalent to revisiting the same 'node' when you represent nodes across multiple rows — Padders, May 22 '23 at 13:27

MT0 · Answer 1 · 2023-05-22T09:23:19.000

From the CONNECT_BY_ISCYCLE documentation (which is linked from the documentation linked in your question):

CONNECT_BY_ISCYCLE Pseudocolumn

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

Therefore, as documented:

NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

and a cycle occurs when:

the current row has a child which is also its ancestor.

Therefore, a hierarchical query with the NOCYCLE clause will return all the rows of the hierarchy and will stop descending the hierachy when it would reach a cycle (i.e. an ancestor in the hierarchy that has already been visited).

If you have the sample data:

CREATE TABLE table_name (parent_id, id) AS
SELECT NULL, 'A' FROM DUAL UNION ALL
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'B', 'C' FROM DUAL UNION ALL
SELECT 'C', 'A' FROM DUAL UNION ALL
SELECT 'C', 'D' FROM DUAL UNION ALL
SELECT 'D', 'B' FROM DUAL UNION ALL
SELECT 'C', 'E' FROM DUAL UNION ALL
SELECT 'E', 'F' FROM DUAL;

Then the query:

SELECT parent_id,
       id,
       SYS_CONNECT_BY_PATH(id, '-') AS path,
       CONNECT_BY_ISCYCLE
FROM   table_name
START WITH
       parent_id IS NULL
CONNECT BY NOCYCLE
       PRIOR id = parent_id
ORDER SIBLINGS BY id;

Outputs:

PARENT_ID	ID	PATH	CONNECT_BY_ISCYCLE
null	A	-A	0
A	B	-A-B	0
B	C	-A-B-C	1
C	D	-A-B-C-D	1
C	E	-A-B-C-E	0
E	F	-A-B-C-E-F	0

The NOCYCLE clause prevents the rows C, A and D, B from being displayed as they connect to an ancestor (as defined by the id column used in the CONNECT BY clause) that has already been visited in the hierarchy and sets CONNECT_BY_IS_CYCLE to 1 on the parent rows B, C and C, D (respectively) as they have cyclic descendants.

If you want to display those cyclic rows then you can include a self-join to ensure that you get the next row in the hierarchy:

SELECT p.parent_id,
       p.id AS current_id,
       n.id AS next_id,
       SYS_CONNECT_BY_PATH(p.id, '-') || '-' || n.id AS path,
       CONNECT_BY_ISCYCLE
FROM   table_name p
       INNER JOIN table_name n
       ON (p.id = n.parent_id)
START WITH
       p.parent_id IS NULL
CONNECT BY NOCYCLE
       PRIOR n.ROWID = p.ROWID
ORDER SIBLINGS BY p.id, n.id;

Which outputs:

PARENT_ID	CURRENT_ID	NEXT_ID	PATH	CONNECT_BY_ISCYCLE
null	A	B	-A-B	0
A	B	C	-A-B-C	0
B	C	A	-A-B-C-A	1
B	C	D	-A-B-C-D	0
C	D	B	-A-B-C-D-B	1
B	C	E	-A-B-C-E	0
C	E	F	-A-B-C-E-F	0

Which accurately identifies the A, B, C, A and A, B, C, D, B cycles in the hierarchy and the NOCYCLE clause, again, allows the SQL engine to process the data without getting into an infinite loop. In this case, the cycle is detected using the p.ROWID pseudo-column (which is effectively a pointer to the row's location in the data file) but the INNER JOIN allows the next row in the hierarchy to be fetched and will include the row with the cycle alongside the previous row and would detect that the cycle in the next iteration of the hierarchy.

fiddle

Not true. As I wrote in the OP, in some cases the query will not stop upon entering a loop. Nor do the docs say when or whether the query will stop. All they say is that it won't error out. — Dmitry E., May 20 '23 at 23:06
Perhaps rather than just repeatedly stating that the documentation and other sources are wrong it would help to provide specific examples of the 'cases' you are referring to? — Padders, May 21 '23 at 07:37
@DmitryE. As @Padders says, please [edit your question](https://stackoverflow.com/posts/76297241/edit) and provide a [MRE] with a complete example with the `CREATE TABLE`, `INSERT` and a hierarchical query which does not have the documented behaviour because, AFAIK, hierarchical queries do behave exactly as documented and the burden is on you to demonstrate that the official Oracle documentation is wrong. — MT0, May 21 '23 at 09:04
@MT0: Hierarchical queries do behave exactly as documented - I said as much in my question. The problem is that documentation does not say what you and many others assume it says: specifically, it does not say when and whether a NOCYCLE query will terminate. I have added an example to my post. — Dmitry E., May 21 '23 at 12:43
@Padders I did not say that Oracle documentation is wrong, but thank you for the suggestion. I have added an example to my post. — Dmitry E., May 21 '23 at 12:44

What exactly does Oracle NOCYCLE parameter do in hierarchical queries?

1 Answers1

`CONNECT_BY_ISCYCLE` Pseudocolumn

What exactly does Oracle NOCYCLE parameter do in hierarchical queries?

1 Answers1

CONNECT_BY_ISCYCLE Pseudocolumn

`CONNECT_BY_ISCYCLE` Pseudocolumn