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';

Answer 1

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