Oracle 递归运行原理

太过爱你忘了你带给我的痛 2022-06-05 10:13 126阅读 0赞

# 一、准备数据 #

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;

# 二、运行sql #

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

--------------------

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

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;

<table> 
 <thead> 
  <tr> 
   <th>序</th> 
   <th>level</th> 
   <th>rownum</th> 
   <th>id</th> 
   <th>x</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
   <td>51</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>2</td> 
   <td>1</td> 
   <td>51</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>3</td> 
   <td>1</td> 
   <td>51</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>4</td> 
   <td>1</td> 
   <td>51</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>1</td> 
   <td>5</td> 
   <td>2</td> 
   <td>6</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>1</td> 
   <td>6</td> 
   <td>3</td> 
   <td>45</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>1</td> 
   <td>7</td> 
   <td>4</td> 
   <td>37</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>1</td> 
   <td>8</td> 
   <td>5</td> 
   <td>91</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>1</td> 
   <td>9</td> 
   <td>6</td> 
   <td>51</td> 
  </tr> 
 </tbody> 
</table>

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