Oracle中关于rownum和分页的详细描述(翻译)

原文为:http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Limiting Result Sets
rownum是一个很让人疑惑的oracle概念，这会导致很多人不会使用它，一般情况下，在以下的两个场合，我们会使用到rownum伪列:

执行一个TOP-N查询，如查询最大或最多，最靠前的某些记录，与其它数据库(如mysql)的limit很相似.
对查询进行分页，这个很常见，就不说了。

How ROWNUM Works
rownum是一个伪列，在一个查询中，它并不是真正的列，但在查询结果中就会存在这个列数据。在查询的结果中，rownum会依次从1开始赋值给查询的每一条记录。但这个值并不是固定的，会随着不同的查询发生变化，也就是说在一个数据表中，并不存在rownum这个列，即你不能在一个表中查询rownum为5的数据，没有这种数据。

那么rownum是什么时候被赋值的呢，了解这个时机会让你在一些查询中了解一个sql为什么要这样写。
rownum是在当数据查询一定的条件被查询出来但并没有被排序或者聚合的时候赋值的
也就是说，rownum是在有数据的时候才会递增，如果没有数据被查询出来，则不会递增。如下面的sql语句，则永远不会返回任何数据:

select * 
  from t 
 where ROWNUM > 1;

因为对于第一条数据来说,rowum > 1并不为真,因此rownum也不会递增到2，所以不会有数据的rownum大于1。
我们可以通过下面的一个标准的数据查询，来了解rownum是何时被赋值的。如下所示：

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

那么，一般来说，它会按照以下的顺序来工作:

The FROM/WHERE clause goes first.
ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
SELECT is applied.
GROUP BY is applied.
HAVING is applied.
ORDER BY is applied.

这就是为什么下面的排序操作是错误的：

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

这个sql语句想查询出工资最高的5个人，但这个查询可能并不会这样工作。但实际上，这个查询会随机查询出5条记录(之所以是随机，是因为这个并没有排序标准，无法保证查询的结果是按某一个顺序排列,尽管一般按照数据存储顺序排序),然后再进行排序。这个sql实际的执行记录可以参考下面的伪代码来实现:

ROWNUM = 1
for x in 
( select * from emp )
loop
    exit when NOT(ROWNUM <= 5)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

它获取了最开始的5条记录，然后再进行排序操作。但如果使用rownum=5或rownum>5并不会工作。这是因为rownum是在一个被满足条件之后才进行赋值，然后在这个记录满足where条件之后再进行递增。如果要正确的工作，则应该向以下的代码这样来写：

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

即先按工作排序，然后再查询前5条记录。但oracle是不是总是先把所有数据进行排序之后再进行过滤的呢，当然不是，这样的效率太差了。针对这个TOP-N查询，oracle足够的智能能达到不需要完整的排序就可以选出满足条件的记录。
为什么，想一下经典的数据算法，从一堆数字中找出N个最大的数字。

Top- N Query Processing with ROWNUM

在一个TOP-N查询中，你可能会想到是先按照整个复杂的查询把所有数据查询出来，然后再进行排序，最后找出最靠前的N条数据，但实际上rownum针对这个情况有一个TOP-N算法优化。你可以使用rownum来避免针对一个大数据的排序。
假如你有一个以下的SQL语句:

select ... 
  from ... 
 where ... 
 order by columns;

假设这个查询返回100万条数据或者更多，但我们只对其中的TOP N，如10条，100条感兴趣。有以下2种处理方法:

客户端查询出所有数据，然后取出其中的N条
使用rownum对数据进行限制，例如使用查询select * from (原查询记录) where rownum <= N

很明显，第2个远远优胜于第1种方法。1个原因是大大减少了客户端的压力和工作，因为数据库已经帮你作了这件事。更重要的原因是数据库可以以更有效率的方法来帮你完成这件事。使用TOP-N查询，意味着你告诉数据库：我只关心这N条记录，其它的记录我不关心。也就是说，其它的记录是否排序这个是可以考虑的。我们以以下的例子来解决这个查询。第一个查询如下：

select * 
  from t 
 order by unindexed_column;

假设t表是一个大表，有100万数据，并且每一条记录都很大，如超过100个字段，并且排序的列并没有建索引。然后，假设你只需要10条数据，那么整个逻辑可以如下所示:

运行一个在t表上的全表扫描
针对排序列进行排序，这是一个全排序。
如果整个排序已经把内存占完了，则需要先把一些数据先刷新到硬盘上，这个直到所有数据全排序完毕。
找出其中的前10条记录。
清空之前用到的临时硬盘数据以及内存信息。

这整个逻辑是一个巨大的IO操作。数据库可能将整个数据表copy到临时空间，但却仅仅为了取其中的10条记录。
但如果oracle使用一个TOP-N查询，则sql语句如下所示：

select *
  from 
(select * 
   from t 
  order by unindexed_column)
 where ROWNUM < :N;

这时,oracle会按以下的步骤来执行：

运行一个全表扫描，这个真没方法避免。
针对一个N条数据的数组，进行排序操作。

我们来看第2个逻辑如何执行，首先把前10条记录，放到数组中，然后进行排序。当第11条数据获取时，只需要对数组中最后一条记录进行比较。如果小于(假设排序列惟一)，则直接抛弃,否则丢弃最后一个数据，然后加个这个数据对数组重新排序。一直进行这个操作，直到所有的数据都执行完毕。在整个操作中，永远都只需要对10条数据进行排序，而不是上100万条数据。
这就意味着只需要很少的空间和更高效的速度。因为，只需要很少的内存就可以完成这件事。

为了有足够的比较，我们可以运行下面的SQL，来一个实际的运行比较。如下所示：

create table t
as
select dbms_random.value(1,1000000) 
id, 
       rpad('*',40,'*' ) data
  from dual
connect by level <= 100000;

begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/

Now enable tracing, via

exec 
dbms_monitor.session_trace_enable
(waits=>true);

然后，使用TOP-N查询来针对表t进行查询:

select *
  from
(select *
   from t
  order by id)
where rownum <= 10;

然后，再运行一个我们自实现的只找出10条记录:

declare
cursor c is
select *
  from t
 order by id;
l_rec c%rowtype;
begin
    open c;
    for i in 1 .. 10
    loop
        fetch c into l_rec;
        exit when c%notfound;
    end loop;
    close c;
end;
/

然后，我们可以使用TKPROF工具来对trace文件进行格式化，以查看在整个过程中发生了哪些操作。以下是第一个查询：

select *
  from
(select *
   from t
  order by id)
where rownum <= 10
 call         count     cpu      elapsed   disk     query      current    rows
--------     --------  -------  -------   -------  --------   --------   ------ 
Parse        1         0.00     0.00      0          0        0           0
Execute      1         0.00     0.00      0          0        0           0
Fetch        2         0.04     0.04      0        949        0          10
--------     --------  -------  -------   -------  --------   --------   ------ 
total        4         0.04     0.04      0        949        0          10

Rows                         Row          Source Operation
-----------------            ---------------------------------------------------
10                           COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10                           VIEW  (cr=949 pr=0 pw=0 time=46979 us)
10                           SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)

这个查询语句读取整个数据表，然后使用SORT ORDER BY STOPKEY操作，这个操作可以限制整个临时空间只有10条记录。需要注意最后一个操作，它执行了949次逻辑读，但并没有物理读和物理写，只花费了0.04秒(46997us)。然后比较一下我们自己作的查询的trace文件:

SELECT * FROM T ORDER BY ID
call         count     cpu      elapsed   disk     query      current    rows
--------     --------  -------  -------   -------  --------   --------   ------ 
Parse         1        0.00     0.00        0        0        0           0
Execute       2        0.00     0.00        0        0        0           0
Fetch        10        0.35     0.40      155      949        6          10
--------     --------  -------  -------   -------  --------   --------   ------ 
total        13        0.36     0.40      155      949        6          10

Rows                         Row          Source Operation
-----------------            ---------------------------------------------------
10                           SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000                       TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)

Elapsed times include waiting for the following events:

Event waited on                  Times
------------------------------   ------------
direct path write temp           33
direct path read temp             5

可以看出，这是完全不一样的。首先，在cpu占用上，第2个比第1个查询所占用的cpu要多得多。同时，最后的操作已经揭示了为什么这么高。因为你必须执行一个基于硬盘的排序，有891次物理写，毕竟要排序100万条数据，这个操作消耗了很多的资源和cpu.

Pagination with ROWNUM

最常见的用法还是使用rownum进行分页操作。一个标准的分页查询如下所示：

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

FIRST_ROWS(N)：告诉优化器，我只关注这些数据，只需要尽可能的获取这些数据信息。
MAX_ROW_TO_FETCH：这个是指我们要获取的数据的最后一条是多少，如果想到获取50－60条数据，只需要将其设置为60即可。
MIN_ROW_TO_FETCH：这个是指从哪一行开始获取，如上所示，这里应该是50.

在一些常见的WEB浏览器中，用户进行一个搜索操作，然后等待结果。这个查询肯定需要尽可能快地返回第1页结果，然后是第2页，第3页……这样,ORACLE只需要执行一个TOP-N算法，然后返回我们需要的数据，并且通过网络只传递这些需要的数据信息，其它不需要的数据仍不会消耗网络流量。

在这个查询里面，一个非常重要的就是你的排序条件：
排序的ORDER BY应该尽可能地基于一个惟一的条件
如果你的排序条件并不是惟一的，那么你应该再追加一些条件以保证排序能惟一。如果你对100个金额进行排序，假如他们都是相同的数据。那么，你期望获取第20条至25条数据，这个并没有什么意义。为了演示这种情况，我们可以创建一个数据表并且用许多相同的数据进行填充，如下所示：

SQL> create table t
  2  as
  3  select mod(level,5) id, 
     trunc(dbms_random.value(1,100)) data 
  4    from dual
  5  connect by level <= 10000;
Table created.

首先，查询第148至150条的数据信息,然后再查询148至151条的数据，如下所示：

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 150
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             38             148
0             64             149
0             53             150

SQL>
SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id) a
  8   where rownum <= 151
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             59             148
0             38             149
0             64             150
0             53             151

我们来看这个rnum为148的数据，在第一次查询中，它的data为38，但是在第二次查询中，即变成了59.但我们并不能说这个数据是错的，也就是说，这2种结果都是对的。即首先根据id排序，然后丢弃前147条数据，再返回3或4条数据。之所以会出现这种情况，是因为我们的排序条件id大多数都是重复的，数据库不能保证每一次排序都是一样的。即第1次排序的情况和第2次排序的绝对顺序可能并不一样。为了解决这个问题，我们需要为这个查询增加一些惟一的信息，即使用ROWID:

SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id, rowid) a
  8   where rownum <= 150
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             45             148
0             99             149
0             41             150

SQL>
SQL> select *
  2    from
  3  (select a.*, rownum rnum
  4     from
  5  (select id, data
  6     from t
  7   order by id, rowid) a
  8   where rownum <= 151
  9  )
 10   where rnum >= 148;

 ID           DATA           RNUM
-------       ----------     -----------
0             45             148
0             99             149
0             41             150
0             45             151

这个查询就决定最终的顺序了，因为在一个数据表中ROWID是惟一的，所以你可以使用order by id然后再接上ROWID,这样就可以保证最终的排序结果。

ROWNUM Wrap-Up

通过这篇文章，我们可以进一步了解ORACLE，以了解更多的知识:

了解ROWNUM是如何被赋值的，这样就不会写出有问题的SQL查询
在分页查询中，有哪些操作会影响数据处理，这样就可以正确进行分页
了解如何通过TOP-N查询来提交查询效率，以及背后的原理和算法

转载请标明出处:i flym
本文地址:https://www.iflym.com/index.php/code/201308080001.html

作者： flym

I am flym,the master of the site:) 查看flym的所有文章

艾华旭 on 使用guava Striped中的lock导致线程死锁的问题分析
//线程1 加锁获取资源类型1 加锁获取资源类型2 //线程2 加锁获取资源类型1 加锁获取类型类型2 这一段里，每个线程都在没释放第一个锁之前就去申请第二个锁吗？
2023-03-22
zp on Spring中循环引用的处理-1
Spring源码浅度解析观光团
2023-01-13
大白 on spring data 运行时添加JPA Repository
请问下有源码可以参考下吗
2022-12-30
王晶 on Spring中循环引用的处理-1
spring 源码深度解析（第2版）读者慕名而来
2022-12-13
asherdai on spring data 运行时添加JPA Repository
请问有源码吗，目前正需要动态加载JpaRepository，用registerSingleton，结果报错BeanDefinition不存在
2022-07-04

相关文章：

作者： flym

发表评论 取消回复

发表评论取消回复