Oracle 递归运行原理

一、准备数据

create table t_test as 
select 
  rownum  id, 
  cast (dbms_random.value*99 as Integer) x   
from dual 
connect by rownum<7;

-- 查看
select * from t_test;

-	id	x
1	1	51
2	2	6
3	3	45
4	4	37
5	5	91
6	6	51

二、运行sql

select 
  level, 
  rownum, 
  a.* 
from t_test a 
start with a.x=51
connect by rownum < 5;

序	level	rownum	id	x
1	1	1	1	51
2	2	2	1	51
3	3	3	1	51
4	4	4	1	51
5	1	5	6	51

---

这时候我们发现了一系列诡异的结果

         1. id=6的数据rownum=5，与条件rownum<5相悖
         2. id=1的数据出现了4次，怎么出现的

解决这两个问题需要深入一下oracle 递归查询

• start with 指明了递归查询的起始条件，直白的说就是怎么查出 level=1的数据
• connect by 指明了递归查询的递归条件，也就是，level>1时，level层数据怎么与(level-1)层的数据衔接；可以使用prior
• 对于问题1，错误的以为rownum<5会限制level=1的数据
  level=1级，执行[from], 查询出一条数据，并置rownum=1，符合[start with]条件x=51;执行 [select];
   level=2级，执行[from],查询出一条数据，置rownum=2，符合条件rownum<5，保留; ；
    …
     level=5级，执行[from]，查询出一条数据，置rownum=5，不符合条件rownum<5，抛弃；
  返回level=1级；
  level=1级，执行[from]查询出第二条数据，并置rownum=5，执行 [select];
   level=2级，执行[from]查询出一条数据，置rownum=6，不符合条件rownum<5，抛弃；
  返回level=1级；
  level=1级，执行[from]查询出第三条数据，并置rownum=6，不符合条件x=51
   …
  查询结束。
• 对于问题2，解决完问题1后发现，与递归关系不大了，因为递归条件中只有rownum限制，没有对实际业务数据限制，这里跟oracle的存取数据的方式有关，t_test表没有主键，没有索引，oracle按照存储顺序取的数据

好先写到这里问题基本解决，接下来一些更复杂的疑问也就迎刃而解了！吃饭去了。

再次回归：

三、再运行sql

select 
  level, 
  rownum, 
  a.* 
from t_test a 
-- start with a.x=51 -- 去掉初始条件
connect by rownum < 5;

序	level	rownum	id	x
1	1	1	1	51
2	2	2	1	51
3	3	3	1	51
4	4	4	1	51
5	1	5	2	6
6	1	6	3	45
7	1	7	4	37
8	1	8	5	91
9	1	9	6	51

这时候我们再看出现的结果，不再诡异了