Oracle層次查詢中connect_by_iscycle偽列的取值研究
這半年過的很充實,無暇顧及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)都將被納入迴圈的判斷條件,使得即使迴圈的行也能被正常的輸出
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/53956/viewspace-2141604/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【層次查詢】Hierarchical Queries之CONNECT_BY_ISCYCLE偽列
- 【層次查詢】Hierarchical Queries之CONNECT_BY_ISLEAF偽列
- Oracle層次化查詢Oracle
- informix 中層次/樹型/Hierarchical查詢的使用ORM
- 在Oracle層次查詢中給SIBLINGS排序Oracle排序
- 0629_層次查詢
- Oracle層次查詢和分析函式在號段選取中的應用Oracle函式
- Oracle層次查詢和分析函式在號段選取中的應用(轉)Oracle函式
- oracle中對LONG列進行查詢Oracle
- 列車車次查詢-餘票查詢-Api介面API
- 【層次查詢】Hierarchical Queries之“樹的遍歷”
- 層級查詢並將層級拆分成多列
- 【層次查詢】Hierarchical Queries之LEVEL應用
- oracle偽列rownumOracle
- Oracle level偽列Oracle
- 二維陣列中的查詢陣列
- ORACLE 中ROWNUM(偽列)用法解析(轉載)Oracle
- 資料庫開發基礎--層次查詢+資料庫
- 資料庫開發基礎---層次查詢資料庫
- 【層次查詢】Hierarchical Queries之處理順序
- Oracle多層級查詢相容的效能問題Oracle
- oracle查詢語句查詢增加一列內容Oracle
- 【JZOF】二維陣列中的查詢陣列
- ORACLE Flashback Query偽列Oracle
- 【--SQL基礎-->層次化查詢(START BY ... CONNECT BY PRIOR)】SQL
- Mybatis中Oracle的拼接模糊查詢MyBatisOracle
- GBase 庫中查詢表的列資訊
- 查詢陣列中第K大的元素陣列
- 九度 二維陣列中的查詢陣列
- 面試題——二維陣列中的查詢面試題陣列
- 陣列中查詢給定值陣列
- 【SQL 學習】層次化查詢之CONNECT BY 和 START WITHSQL
- 組合索引的前導列與查詢——ORACLE索引Oracle
- 關於Oracle偽列rownumOracle
- 記一次詭異的Oracle查詢轉換Oracle
- 查詢oracle中的隱形引數Oracle
- 15、Oracle中的高階子查詢Oracle
- 【劍指offer】二維陣列中的查詢陣列