Oracle分层查询中NOCYCLE参数的具体作用是什么？

多年前Oracle为分层查询引入了可选的NOCYCLE参数，但除了避免在检测到循环时抛出错误外，它的具体作用一直模糊不清。网上多数相关资料存在错误或误导，比如权威的Oracle-Base网站曾错误表述：

CONNECT BY NOCYCLE子句告知数据库不要遍历循环层级结构。

Oracle官方文档的表述则较为含糊：

NOCYCLE参数指示Oracle数据库即使数据中存在CONNECT BY循环，仍返回查询结果。

这个表述属实，但并未说明查询遇到循环时是否会停止遍历，以及何时停止。

实际测试发现：使用CONNECT BY NOCYCLE遍历循环图时，Oracle在某些场景下会进入循环，但在完成循环前停止并返回结果；但在其他场景下，可能会陷入无限循环。（上述Oracle-Base的文章确实提到过无限循环的可能性，但并非在同一语境下。）

核心问题 #

带有NOCYCLE子句的分层查询在处理循环时是否有明确的规定行为？

无限循环复现示例 #

以下是可复现的无限循环测试案例：

创建表语句 #

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

查询脚本 #

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

内容的提问来源于stack exchange，提问作者Dmitry E.