這半年過的很充實，無暇顧及blog，還好闊別不長，興致未減。
表裡的記錄若存在上下級關係，藉助層次查詢(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層次查詢中connect_by_iscycle偽列的取值研究

相關文章