深入讨论非树形结构下使用connect by产生的结果集问题

Question

connect by最常用于树的遍历,而利用它的递归特点,我们也一般用它来构造序列.SQL> select rownum from dual connect by rownum<5;    ROWNUM
----------
         1
         2
         3
         4SQL> select level from dual connect by level<5;     LEVEL
----------
         1
         2
         3
         4而之所以能构造出序列,还有一个原因是因为dual表只有一行数据,换句话说,
找任何一个只有一行数据的表,都能替换上面的dual而不会改变结果集,而空表和多行表就不行了.
空表的结果集肯定为空,而多行表就是我想讨论的问题.先看最简单的语句SQL> select * from test;DATA
----------
tom
jerrySQL> SELECT data, rownum, LEVEL FROM test CONNECT BY rownum < 5;DATA           ROWNUM      LEVEL
---------- ---------- ----------
tom                 1          1
tom                 2          2
tom                 3          3
tom                 4          4
jerry               5          1SQL> SELECT data, rownum, LEVEL FROM test CONNECT BY LEVEL < 5;DATA           ROWNUM      LEVEL
---------- ---------- ----------
tom                 1          1
tom                 2          2
tom                 3          3
tom                 4          4
jerry               5          4
jerry               6          3
tom                 7          4
jerry               8          4
jerry               9          2
tom                10          3
tom                11          4
jerry              12          4
jerry              13          3
tom                14          4
jerry              15          4
jerry              16          1
tom                17          2
tom                18          3
tom                19          4
jerry              20          4
jerry              21          3
tom                22          4
jerry              23          4
jerry              24          2
tom                25          3
tom                26          4
jerry              27          4
jerry              28          3
tom                29          4
jerry              30          4level<5这句虽然复杂,却很好理解,因为没有约束父子关系,
所以每个结点都可以成为自己或别人的子结点,tom和jerry都可以成为root结点,
而它们又都可以成为这两个root结点的子结点,在level<5的限制下,
2(root)+4(第二层)+8(第三层)+16(第四层)=30行,和结果集一致.令我难以理解的是rownum<5这句,很明显connect by后的rownum<5并没有起到限制结果集为4条的作用.
进一步试验表明,如果表内是M表数据,条件是connect by rownum<N(N>M),那么结果集就是M+N-2行.
给人的感觉是connect by rownum<N限制了每个表第一行的数据,也就是说会出现
DATA           ROWNUM      LEVEL
---------- ---------- ----------
tom                 1          1
tom                 2          2
tom                 3          3
tom                 4          4
tom                ...        ...
tom                N-1        N-1
而后面的行
jerry               N          1
.....              N+1         1
.....              ...         1
.....             M+N-2        1
都只出现一次,并且level都为1.
还没完,令人更困惑的还在后面.
用where和start with可以过滤上面的结果集,
对于level<5的,没什么问题.而对于rownum<5的,用start with时
SQL> SELECT data, rownum, LEVEL
  2    FROM test
  3   START WITH data = 'tom'
  4  CONNECT BY rownum < 5;DATA           ROWNUM      LEVEL
---------- ---------- ----------
tom                 1          1
tom                 2          2
tom                 3          3
tom                 4          4SQL> 
SQL> SELECT data, rownum, LEVEL
  2    FROM test
  3   START WITH data = 'jerry'
  4  CONNECT BY rownum < 5;DATA           ROWNUM      LEVEL
---------- ---------- ----------
jerry               1          1
tom                 2          2
tom                 3          3
tom                 4          4rownum<5的条件又起作用了,结果集都为4条,而且也好理解.
为什么有了start with就能限制结果集了?不懂.而用where条件
SQL> SELECT data, rownum, LEVEL
  2    FROM test
  3  WHERE data = 'tom'
  4  CONNECT BY rownum < 5;DATA           ROWNUM      LEVEL
---------- ---------- ----------
tom                 1          1
tom                 2          2
tom                 3          3
tom                 4          4tom是这个表的第一条记录,用where没问题,结果集和用start with一样.但用jerry时
SQL> SELECT data, rownum, LEVEL
  2    FROM test
  3  WHERE data = 'jerry'
  4  CONNECT BY rownum < 5;
我机子的内存耗尽,估计是死循环了.
进一步试验发现,如果表有多条记录,只要where后面的不是第一条,必定死循环.
想搞清楚原因,估计先得搞清楚为什么在没有条件限制的情况下,
CONNECT BY rownum < N的结果集的是那个样子.
希望对这个问题有心得的朋友出来指点一下,大家多多讨论.

Answer 1

支持，我随便建了个表试了下，rownum是起作用的，不知道为什么你的是那样

Answer 2

如果表内是M表数据,条件是connect by rownum <N,要求N>M才会有令我不解的结果.

Answer 3

确实有这个问题，不知道connect by的内部实现怎么做的？
应该是递归或者循环的算法吧，可是为啥不能返回了呢？
不知道CSDN是否有真的高手在。 

Answer 4

我的就是 N > M 呀，难道是版本不一样？我的是9iSQL> select * from test;
 
DATA
--------------------
tom
jerry
 
SQL> SELECT data, rownum, LEVEL FROM test CONNECT BY rownum < 5;
 
DATA                     ROWNUM      LEVEL
-------------------- ---------- ----------
tom                           1          1
tom                           2          2
tom                           3          3
tom                           4          4

Answer 5

刚查了一下，原来10g的connect by有改动，你可以baidu一下，呵呵