深入理解connect by level<xxx

深入理解connect by  rownum

对于connect by，现在大多数人已经很熟悉了

connect by中的条件就表示了父子之间的连接关系
比如 connect by id=prior  pid

但如果connect by中的条件没有表示记录之间的父子关系（如 connect by里没有 id=prior  pid）
那会出现什么情况？
常见的，connect by会在构造序列的时候使用。

用select rownum fromdual connect by rownum<xxx 来代替早期版本（中使用）的 select rownum fromall_objects where rownum <xxx。

我们注意到，dual是一个只有一条记录的表，如果表有多条记录，将会怎样？

下面开始实验

SQL> create table aaa(id varchar2(1));

 

Table created.

 

SQL> insert into aaa (id) values ('a');

 

1 row created.

 

SQL> insert into aaa (id) values ('b');

 

1 row created.

 

SQL> insert into aaa (id) values ('c');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select id,level from aaa connect by level<2;

 

I      LEVEL

- ----------

a          1

b          1

c          1

 

SQL> select id,level from aaa connect by level<3;

 

I      LEVEL

- ----------

a          1        <---此处开始第一个子叶树分支

a          2

b          2

c          2

b          1       <---此处开始第二个子叶树分支

a          2

b          2

c          2

c          1       <---此处开始第三个子叶树分支

a          2

b          2

c          2

 

12 rows selected.

 

SQL> select id,level from aaa connect by level<4;

 

I      LEVEL

- ----------

a         1  <---此处开始第一个子叶树分支

a         2

a         3

b         3

c         3

b         2

a         3

b         3

c         3

c         2

a         3

b         3

c         3

b         1  <---此处开始另一个子叶树分支

a          2

a          3

b          3

c          3

b          2

a          3

b          3

c          3

c          2

a          3

b          3

c          3

c         1  <---此处开始另一个子叶树分支

a          2

a          3

b          3

c          3

b          2

a          3

b          3

c          3

c          2

a          3

b          3

c          3

 

39 rows selected.

下图为select id,level from aaa connect by level<4时递归查询到的树状结构：

由上图，可以得出规律如下：

N+N的二次方+。。。。。+N的LEVEL次方

其中，N表示表中有N条记录，LEVEL表示上述树状图中的树的层数，也就是指connect by 子句中的level伪列（或是rownum伪列）值。

树每增加一层，则N+N的二次方+。。。。。+N的LEVEL+1次方=N+N*（N+N的二次方+。。。。。+N的LEVEL次方）。

于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)，即level=1时，power(3,1)=3  level=2时，power(3,2)=9，即12-3，level=3时，power(3,3)=27，即39-12。

从而得出如下结论：

假设表中有N条记录，则记F(N,l)为select id,level from t connect by level<l 的结果集数目。那么：
F(N,1)=N
F(N,l) = F(N,l-1)*N+N

注释：


当连接条件（connect by条件）没有限制记录之间的关系（即 connect by里没有类似 id=prior  pid的条件，而是 connect by rownum<xxx 或 connect by level<xxx ）时，每一条记录都会作为自己或者其他记录的子节点，也就说，每一条记录的子节点就是表上所有的记录。而树的层数就是rownum（或是level）值。

这就是Oracle采用了深度优先的算法。

另外见：

《Oracle层级查询语句（hierarchical query）connect by 用法详解》

参考：

百度   connect by rownum