解析Oracle数据扫描OracleSQL优化引导局部扫描方法（4）

 

灵活应用ROWNUM虚拟列

 

    Rownum并不是表中实际存在的物理列，而是一个可以被使用在任何SQL语句中的虚拟列。如果能够正确的理解Rownum概念和特性，那么就可以将其应用在Where条件中，引导优化器只处理指定的行数，因此可见这属于局部范围扫描范畴。但是如果不了解Rownum的特性就草率的使用它，则有可能无法输出期望的结果，同时也无法按照设想的数据扫描方式运行。现在讨论一下如何利用Rownum的特性实现局部范围扫描。  www.2cto.com  

 

特性一：应保证数据范围足够大。看如下示例语句：

 

    Select * from t where tno>100 and rownum<=10;该语句扫描满足条件的数据并将结果记录赋予Rownum值，当Rownum超过10时停止查询执行返回结果。这是理想情况下的局部范围扫描，但是如果满足tno>100的值只有不足10条数据，那么执行引擎为了满足Rownum<=10的条件就可能扫描更多的数据，最坏的情况下是扫描了满足所有条件的数据后返回实际的记录行，这与我们的初衷事与愿违，其中的原因只是满足条件的数据范围过窄，无法尽快填充容纳结果的批量数组。因此若想利用Rownum实现针对大数据量范围扫描，那么首先要保证满足驱动查询条件的数据范围够大，最好远远超过语句中Rownum设定的数目。

 

特性二：Rownum的值是在执行内部存储操作之前被赋予的，并与对应的数据行一起被存储。该特性往往会影响带有Rownum虚拟列的排序（order by）或者分组（group by）查询语句的结果集。看下面的示例语句：

 

Select rownum,item_cd,catory_cd

 

From product

 

Where deptno like ‘120%’ and qty>0 and rownum<=10

 

Order by item_cd;  www.2cto.com  

 

        执行该语句的本意可能是要对所有满足条件的数据执行排序后，然后输出前10条记录，但是这条语句其实无法实现最初的目的。因为在Order by被执行之前，已经为满足Where中的所有查询条件的数据行赋予了Rownum并将其保存在了临时表空间中，由于在排序操作执行之前已经完成了对Rownum<=10的过滤，所以最终的Order by操作也只是对10行数据进行排序而已，因此根本无法按照Rownum的真实顺序输出，而只是将Order by后的结果直接进行了输出。

 

       之所以会这样，本质原因是由于是因为这个Rownum的特性，使得在排序操作过程中，按照了全局范围扫描进行了数据读取。该SQL必须按照全部范围扫描满足条件的所有数据，并将它们存放在内部临时存储区域，然后执行排序操作，而Rownum的值是在执行内部存储操作之前被赋予的，并与对应的数据行一起被存储。所以在查询条件中存在Rownum限制条件时，排序并不会按照满足条件的整体数据范围来排序，而只会按照Rownum限定的范围来排序，因此真实的结果与期待的结果会发生差异。

 

同样的问题也会存在于使用Rownum限制进行分组操作时，如下面语句所示：

 

Select deptno,sum(qty)

 

From product

 

Where deptno like ‘120%’ and rownum<=10

 

Group by deptno;

 

      同样由于Rownum的这个特性，最终也只会针对10行数据进行分组。那么怎么来解决这个问题，来使得即按照局部范围扫描来运行语句，又能按照期待的查询结果显示数据呢？可以通过采用动态视图的方法来改写上述语句，实现我们的目的，如下所示：

 

排序操作：

 

Select rownum,item_cd,catory_cd

 

From（select * from product where deptno like ‘120%’ and qty>0

 

      Order by item_cd)

 

Where rownum<=10;

 

分组操作：

 

Select rownum,deptno,total

 

From (Select deptno,sum(qty) total

 

From product  www.2cto.com  

 

Where deptno like ‘120%’

 

Group by deptno)

 

    Where rownum<=10;

 

        很显然这里并非是完全意义上的局部范围数据扫描，因为构成动态视图的子查询会以全部范围数据扫描来执行，但语句整体上会以局部范围扫描来执行，但此时局部范围扫描的性能就会非常依赖于子查询的查询性能，这其实也是一种迫不得已的折中方案，因此是使用时要格外注意。

 

特性三：Rownum是以逻辑集合为单位而存在的，即有一个select语句就会有一个对应的Rownum。灵活应用该特性可以实现只输出特定Rownum或者某一范围Rownum结果集的目的。看如下应用实例：

 

Select t2.deptno,t2.totqty

 

From(select rownum as rnum,t1.deptno,t1.totqty

 

     From(select deptno,sum(qty) as totqty

 

         From product  www.2cto.com  

 

         Where deptno like ‘120%’ and rownum<=100

 

         Group by deptno) t1

 

    ) t2

 

Where rnum<=5 and rownum=1;

 

         该语句实现了从内部子查询的分组统计结果中只输出第5行数据，之所以会实现，主要是因为利用了每一级别的子查询（每一个Select语句）中产生的Rownum作用与意义不同。子查询t1中的Rownum表示的是满足查询条件结果集的Rownum；子查询t2中的Rownum通过别名命名为rnum，表示分组操作(group by)后最终结果的Rownum；由于在没有使用其他限制条件的情况下，即使找到了rnum=5的数据行扫描仍然不会停止，而是继续会扫描从子查询中所接受的所有数据行，一直到结束为止。因此在最外层的查询中加上rownum=1的限制条件，使得找到rnum=5的数据后即刻停止扫描，避免读取多余的数据。

 

        还有一种利用Rownum此特性且非常常见的应用，那就是实现分页查询，在日常的应用系统开发过程中，分页查询的需求非常常见，通过分页查询来分散大范围数据扫描对查询性能的压力。看如下语句实例：

 

Select * from(Select rownum rn,deptno,totqty

 

From (select deptno,sum(qty) as totqty

 

From product  www.2cto.com  

 

Where deptno like ‘120%’

 

Group by deptno)

 

                Where rownum<=50) t1

 

     Where rn>=1;

 

该语句实现了从内部子查询的分组统计结果中输出，从第1条到第50条结果记录的功能。因此通过使用Rownum可以实现控制扫描输出的记录条数和记录范围。

 

 

 

摘自 javacoffe的专栏