Oracle层次查询中connect_by_iscycle伪列的取值研究

107次阅读

共计 4348 个字符，预计需要花费 11 分钟才能阅读完成。

表里的记录若存在上下级关系，借助层次查询 (Hierarchical query) 能将记录按照树状形式输出，关于层次查询这里不展开介绍。
我们要研究的是当表中的上下级记录之间存在循环关系时，Oracle 是如何把这些引起循环的行标记出来的。

##### 创建测试用表
drop table scott.t0704_1;
create table scott.t0704_1(tn varchar2(1),fatherid number,childid number);
insert into scott.t0704_1 values(‘A’,null,1);
insert into scott.t0704_1 values(‘B’,1,2);
insert into scott.t0704_1 values(‘C’,1,3);
insert into scott.t0704_1 values(‘D’,2,4);
insert into scott.t0704_1 values(‘E’,4,1);
insert into scott.t0704_1 values(‘F’,4,5);
commit;

select * from scott.t0704_1;

TN FATHERID CHILDID
— ———- ———-
A 1
B 1 2
C 1 3
D 2 4
E 4 1
F 4 5

6 rows selected

##### 执行层次查询
SQL> select tn,fatherid,childid,ltrim(sys_connect_by_path(childid,’->’),’->’) rel,level from scott.t0704_1 start with fatherid is null connect by prior childid=fatherid;
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

childid:2 是 childid:1 的后代，childid:4 是 childid:2 的后代，childid:1 又是 childid:4 的后代，即 1 ->2->4->1，其中 1 出现了两次，构成了一个循环，层次结构不确定，所以出现了 ORA-01436 错误

可以在 connect by 之后加入 nocycle，在表内记录层次结构出现循环的情况下依然打印出部分记录，同时利用 connect_by_iscycle 伪列协助标记循环是从哪一行开始的(connect_by_iscycle 必须与 nocycle 连用)

>>>>> Example 1:
col rel format a15
select tn,fatherid,childid,connect_by_iscycle,ltrim(sys_connect_by_path(childid,’->’),’->’) rel,level from scott.t0704_1 start with fatherid is null connect by nocycle prior childid=fatherid;

T FATHERID CHILDID CONNECT_BY_ISCYCLE REL LEVEL
– ———- ———- —————— ————— ———-
A 1 0 1 1
B 1 2 0 1->2 2
D 2 4 1 1->2->4 3
F 4 5 0 1->2->4->5 4
C 1 3 0 1->3 2

SQL Reference 上的对于 connect_by_iscycle 的解释是：
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0. 当前行的后代同时也是当前行的祖先时，这一行就会被标示为 connect_by_iscycle=1。按照这一逻辑，在处理到 tn=’D’ 这行时发现 4 的后代是 1，而 1 又是 4 的祖先，所以 tn=’D’ 所在行的 connect_by_iscycle=1，tn=’E’ 这一行使得层次结构上出现了循环就没有输出，这样解释似乎很合情理

我们把查询稍微修改一下: start with fatherid is null=>start with fatherid=1

>>>>> Example 2:
col rel format a15
select tn,fatherid,childid,connect_by_iscycle,ltrim(sys_connect_by_path(childid,’->’),’->’) rel,level from scott.t0704_1 start with fatherid=1 connect by nocycle prior childid=fatherid;

T FATHERID CHILDID CONNECT_BY_ISCYCLE REL LEVEL
– ———- ———- —————— ————— ———-
B 1 2 0 2 1
D 2 4 0 2->4 2
E 4 1 1 2->4->1 3
C 1 3 0 2->4->1->3 4
F 4 5 0 2->4->5 3
C 1 3 0 3 1

按照上面的解释 tn=’D’ 所在行的 connect_by_iscycle 伪列应当被标记为 1，但实际却是 tn=’E’ 这行的 connect_by_iscycle=1。

官档对于 connect_by_iscycle 伪列的解释没错，但不足以解释上述两个查询，对于 connect_by_iscycle 列何时为 1，我的理解如下：
因 connect by 是按照深度优先的原则进行遍历的，在 Example 1 里当遍历了 tn=’D'(fatherid=2、childid=4)后，再往深一层遍历的时候就轮到 tn=’E'(fatherid=4、childid=1)了，此时 childid= 1 已经在 tn=’A’ 所在行输出过一次了，鉴于 tn=’E’ 所在行会导致层次结构上的循环，所以这一行不会被输出，其祖先 tn=’D’ 所在行的 connect_by_iscycle=1。

在 Example 2 里当遍历了 tn=’E'(fatherid=4、childid=1)后，再往深一层遍历的时候就又轮到 tn=’B'(fatherid=1、childid=2)了，此时 childid= 2 已经在 tn=’B’ 所在行输出过一次了，鉴于 tn=’B’ 所在行会导致层次结构上的循环，所以这一行不会被重复的输出第二遍，tn=’E’ 所在行的 connect_by_iscycle=1。

稍加总结：connect by prior c1=f1 作为表内记录层次关联的条件时，在遍历过程中 c1 字段会与祖先节点的 c1 字段进行比较，在 level= m 时遍历到 c1=k，在 level= n 时 (n>m) 又遍历到 c1=k，那么 level= n 时的 c1= k 所在行不会输出，level>n 时以 c1= k 作为祖先的行自然也不会输出；level=(n-1)时 c1= k 的祖先所在行输出且 connect_by_iscycle=1

扩展一下：
若要在 Example 1 的查询中输出 tn=’E’ 所在的行，可以这样改写：
col rel format a15
select tn,fatherid,childid,connect_by_iscycle,ltrim(sys_connect_by_path(childid,’->’),’->’) rel,level from scott.t0704_1 start with fatherid is null connect by nocycle prior childid=fatherid and (prior fatherid is null or prior fatherid is not null);

T FATHERID CHILDID CONNECT_BY_ISCYCLE REL LEVEL
– ———- ———- —————— ————— ———-
A 1 0 1 1
B 1 2 0 1->2 2
D 2 4 0 1->2->4 3
E 4 1 1 1->2->4->1 4
C 1 3 0 1->2->4->1->3 5 <— 因为 ’E’ 输出了，所以其 child:C(level=5)也输出了
F 4 5 0 1->2->4->5 4
C 1 3 0 1->3 2

(prior fatherid is null or prior fatherid is not null)这个条件看似无意义，但却能让 childid、fatherid 两个列都加入到与祖先节点是否相等的判断中，只有这两个列的值都与祖先行相等这一行才不会被输出。概括地讲：connect nocycle by prior c1=f1 and prior c2=f2 … and prior cn=fn，那么 prior 后的所有字段 (c1,c2,…cn) 都将被纳入循环的判断条件，使得即使循环的行也能被正常的输出

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-07/145460.htm

正文完

星哥说事-微信公众号