[20180416]connect by和nocycle.txt

[20180416]connect by和nocycle.txt

--//連結http://www.itpub.net/thread-2101289-1-1.html的討論,感覺在使用nocycle有點困惑,做一個記錄:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t (id number ,a number);
Table created.

insert into t values (1,11);
insert into t values (1,12);
insert into t values (2,12);
insert into t values (3,11);
insert into t values (3,15);
insert into t values (3,16);
commit ;

SCOTT@book> select * from t;
ID   A
--- ---
  1  11
  1  12
  2  12
  3  11
  3  15
  3  16
6 rows selected.

2.測試:
SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t  connect by  nocycle prior id=  id   start with a=11;
      A_ROOT       ISLEAF      ISCYCLE           ID            A
------------ ------------ ------------ ------------ ------------
          11            1            1            1           11
          11            1            1            3           11
--//開始自己非常不理解為什麼僅僅2行輸出.實際上nocycle就指示出現cycle的不輸出.
--//當執行prior id=  id時,取第一行id=1,A=11時就已經形成環路.因為 prior id=  id.
--//這樣id=1,A=12的行永遠不會輸出.

--//如果寫成如下:prior id=  id  and prior a <  a

SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t  connect by  nocycle prior id=  id  and prior a <  a start with a=11;
      A_ROOT       ISLEAF      ISCYCLE           ID            A
------------ ------------ ------------ ------------ ------------
          11            0            0            1           11
          11            1            0            1           12
          11            0            0            3           11
          11            0            0            3           15
          11            1            0            3           16
          11            1            0            3           16
6 rows selected.

--//當取第一行id=1,A=11時,這個prior id=  id  and prior a <  a 就是假,沒有形成環路,這樣可以繼續掃描.
--//而且這樣理論也不存在環路.

SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t  connect by   prior id=  id  and prior a <  a start with a=11;
select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t  connect by   prior id=  id  and prior a <  a start with a=11
                                                                                                                 *
ERROR at line 1:
ORA-30930: NOCYCLE keyword is required with CONNECT_BY_ISCYCLE pseudocolumn

--//這裡是因為使用CONNECT_BY_ISCYCLE,取消這個查詢寫成如下:

SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf,  t.* from t  connect by   prior id=  id  and prior a <  a start with a=11;
      A_ROOT       ISLEAF           ID            A
------------ ------------ ------------ ------------
          11            0            1           11
          11            1            1           12
          11            0            3           11
          11            0            3           15
          11            1            3           16
          11            1            3           16
6 rows selected.

--//當然我的查詢還是不能滿足作者的需求..^_^.