\(^_^)/ Oracle 分页查询

1.根据ROWID来分

select *
  from t_xiaoxi
 where rowid in
       (select rid
          from (select rownum rn, rid
                  from (select rowid rid, cid from t_xiaoxi order by cid desc)
                 where rownum < 10000)
         where rn > 9980)
 order by cid desc;

 

2.按分析函数来分

select *
  from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t)
 where rk < 10000
   and rk > 9980;

 

3.按ROWNUM来分

select *
  from (select t.*, rownum rn
          from (select * from t_xiaoxi order by cid desc) t
         where rownum < 10000)
 where rn > 9980;

 

 

实例：

SELECT * FROM dim_nsr nsr WHERE rowid IN
      (SELECT rid FROM
             (SELECT rownum rn , tmpA.rid FROM
                     (SELECT rowid rid ,n. nsrsbh FROM dim_nsr n order by n.nsrsbh desc) tmpA
             WHERE rownum < 10000) tmpB
      WHERE tmpB.rn >9990) 
ORDER BY nsr.nsrsbh
 
 
SELECT tmp.* from 
   (SELECT n.* ,ROW_NUMBER() OVER(ORDER BY n.nsrsbh desc) rk FROM dim_nsr n) tmp
WHERE rk < 10000
  AND rk >9990 ;
  
  
SELECT tmpB.nsrsbh FROM 
    (SELECT tmpA.*, rownum rn FROM
           (SELECT * FROM dim_nsr n ORDER BY n.nsrsbh desc) tmpA
    WHERE rownum < 10000) tmpB
WHERE rn > 9990 ;

 

 

 

 

 

 

Oracle按ROWNUM分页查询

 

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

分页查询格式：

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TABLE_NAME) A
         WHERE ROWNUM <= 40)
 WHERE RN >= 21 ;

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT *
  FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A)
 WHERE RN BETWEEN 21 AND 40 ;

 

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。

对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。

因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。

下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */
 *
  FROM (SELECT A.*, ROWNUM RN
          FROM (SELECT * FROM TABLE_NAME) A
         WHERE ROWNUM <= 40)
 WHERE RN >= 21 ;

 

 

 

 

 

Oracle按ROWNUM分页查询分析

 

什么是分页查询

对于基于Web的应用而言，对查询的结果集进行分页是一个比较常见的需求。假设浏览器界面每页可以显示10条记录，最初界面显示头10条记录给用户，当终端用户点击“下一页”按钮时，界面显示接下来的10条记录。一般来说，Web后台服务程序并不是一次性的把所有符合条件的记录都返回给浏览器，再由浏览器应用程序对查询结果进行分页。现在的普遍做法都是：当用户要浏览下一页时，浏览器重新从WEB后台服务器取出下10条记录。

对于采用了数据库的WEB应用来说，如何对查询的结果进行分页就有两种实现方式，一种是WEB后台程序把全部查询结果取到内存中，由它实现分页。另一种是每次只从数据库取出10条记录，由数据库实现分页。

这两种分页方式各有优缺点，有时可能把这两种方式结合起来应用。在这里，我主要介绍一下如何在Oracle数据库中实现分页查询。

如何实现分页查询

认识ROWNUM

Oracle的ROWNUM伪列是实现结果集分页的关键，可能有很多人对于ROWNUM伪列到底代表什么还不太清楚，有人甚至认为它是数据库表中记录的编号。下面我引用在ASKTOM网站上的他一个例子帮助大家认识一下ROWNUM到底为何物。为了帮助大家理解，我建了一个测试表，然后再插入20条测试数据，当前原例子中查询语句表名和字段也做了相应的修改。

--建测试表

 

create table t_testrownum
( rid        number,      rvalue     varchar2(30))
--插入测试数据
begin    
        insert into t_testrownum values(1,  'aaaa');
        insert into t_testrownum values(2,  'aaaa');
        insert into t_testrownum values(3,  'aaaa1');
        insert into t_testrownum values(4,  'aaaa');
        insert into t_testrownum values(5,  'aaaa');
        insert into t_testrownum values(6,  'aaaa');
        insert into t_testrownum values(7,  'aaaa');
        insert into t_testrownum values(8,  'aaaa4');
        insert into t_testrownum values(9,  'aaaa');
        insert into t_testrownum values(10, 'aaaa');
        insert into t_testrownum values(11, 'aaaa');
        insert into t_testrownum values(12, 'aaaa');
        insert into t_testrownum values(13, 'aaaa5');
        insert into t_testrownum values(14, 'aaaa');
        insert into t_testrownum values(15, 'aaaa');
        insert into t_testrownum values(16, 'aaaa');
        insert into t_testrownum values(17, 'aaaa');
        insert into t_testrownum values(18, 'aaaa8');
        insert into t_testrownum values(19, 'aaaa');
        insert into t_testrownum values(20, 'aaaa');
    end;

 

 

例1．select * from t_testrownum where rownum = 1; 

 

返回结果集的第一条记录。那么select * from T where rownum = 2;应该返回结果集的第二条记录。可是实际上第二个查询语句不会返回任何记录，为什么呢？

类似的例子还有：

select * from T where rownum >= 1 and rownum <= 5; 返回前5条记录，而

select * from T where rownum >= 2 and rownum <= 5;  无记录返回。

其实：ROWNUM并不是记录编号，而是Oracle在向外输出结果集中的记录时给它赋的一个顺序号。当不在查询语句中限制ROWNUM时，其处理逻辑如下所示：

rownum = 1

for x in ( select * from T )

    loop

    if ( x satisifies the predicate ) 

    then

          OUTPUT the row

          rownum = rownum + 1 

    end if;

end loop;

当限制ROWNUM时，我们对比一下下面两个查询的执行计划：

 

语句1：select * from t_testrownum;

语句1的执行计划：

SELECT STATEMENT Optimizer Mode=CHOOSE          

TABLE ACCESS FULL      USDPD502.T_TESTROWNUM           

 

语句2：select * from t_testrownum where rownum <= 10;

语句2的执行计划

SELECT STATEMENT Optimizer Mode=CHOOSE

COUNT STOPKEY

TABLE ACCESS FULL USDPD502.T_TESTROWNUM

通过对比，我们可以看出语句2的执行计划中增加了一条‘COUNT STOPKEY’，该句的意思是当rownum已超出指定范围时，停止输出，其处理逻辑如下：

rownum = 1

for x in ( select * from T )

    loop

if ( x satisifies the predicate ) 

then

   OUTPUT the row

rownum = rownum + 1 

end if;

if  ( rownum已超出指定范围 ) 

then

   跳出循环

end if;

end loop;

至此，我们就可以解释上面两个例子中的问题了。当我们限制rownum=1时，第一条记录满足该条件，输出该记录，rownum增1，由于rownum已超出范围，停止输出。当我们限制rownum=2时，由于第一条记录不满足条件，不输出该记录，rownum也不增加。接着取第二条记录，由于rownum此时还是1，不满足条件，同样也不输出，如此直到遍历全部记录结束循环。 

基本的分页查询

当知道rownum是怎么回事后，我们就可以利用它来实现分页查询了。假如我们想从表T中取出第11条到第20条记录，在未透彻了解ROWNUM之前，许多人可能会写出下面的查询语句。

 

--语句1

select * from t_testrownum a where rownum >= 11 and rownum <= 20;

通过前面的分析，我们知道，这样的写法是错误的。所以，我们把它修改为如下的写法。   

 

--语句2

select *

  from (select a.*, rownum r from t_testrownum a)

 where r >= 11

   and r <= 20 ;

   

该语句的输出结果是正确的，它的内层查询先从表t_testrownum中查询出所有记录，同时为每条记录赋一个顺序编号r，外层查询再限制只选取编号为11到20之间的记录。

从查询效率上考虑一下，如果我们只需要得到第11到20条之间的记录，那么在内层查询中就可以利用rownum限制内层查询输出的记录数。修改后的语句如下：

 

--语句3

select *

  from (select a.*, rownum r from t_testrownum a where rownum <= 20)

 where r >= 11 ;

 

需要排序的分页查询

有人会想，排序那还不简单吗，加上order by 子句就行了。

 

--语句4

select *

  from (select a.*, rownum r

          from t_testrownum a

         where rownum <= 20

         order by rvalue)

 where r >= 11

我们都知道order by是对输出的结果集进行排序，而不是先排序然后输出结果集。语句4的实际效果是，从表t_testrownum中取出前20行记录，然后按照rvalue字段排序，输出排序编号大于等于11的记录。

只对前20条记录进行排序显然不是我们所期望的，为避免这个问题，有人可能会把上面的语句做如下修改：

 

--语句5

select * 

from ( select a.*, rownum r from t_testrownum a  order by rvalue ）

where r>= 11 and r <= 20 ;

同样，由于rownum 在排序之前就确定了，我们得到得记录并不是排序后的第11到20条记录，而是排序前的第11到20条记录。为得到我们期望的结果，我们必须把rownum r放到order by的外面。修改后的查询语句如下。

 

--语句6

select *

  from (select b.*, rownum r

          from (select a.* from t_testrownum a order by rvalue) b

         where rownum <= 20)

 where r >= 11 ;

如果排序字段rvalue的值在表t_testrownum中是唯一的，那么上面的语句从功能实现上来说，就没什么问题了。但是如果rvalue字段的值不唯一，假设按rvalue排序后，前1到20条记录的rvalue字段的值是相同的，我们先查出第1到10条记录，然后再查出第11到20条记录，这是我们会发现，同一条记录可能同时出现在这两个查询结果集中。这是为什么呢。一开始，我认为是Oracle采用的排序算法是不稳定的，两个相同的值在两次排序中的顺序是不固定的。但是我们把语句select a.* from t_testrownum a order by rvalue执行10次，却发现输出结果集的排序顺序都是一致的。那么是什么导致排序不一致呢。为此，我们观察了一下语句6的执行计划：

SELECT STATEMENT,

       GOAL = CHOOSE VIEW Object owner = USDPD502 COUNT STOPKEY VIEW Object owner = USDPD502 SORT ORDER BY STOPKEY TABLE ACCESS FULL Objectowner = USDPD502 Objectname = T_TESTROWNUM

从执行计划中我们看出，执行计划的第2步是“SORT ORDER BY STOPKEY”，它表示

其并不是对所有符合条件的记录完全排序，而是仅仅找到符合排序条件的指定条数的记录，比如我们限制rownum <= 20，则只需找到排序在前20位的记录。记得在Oracle的官方文档上我曾经见到Oracle声称其排序是稳定的一致的。前面我们将select a.* from t_testrownum a order by rvalue执行10次，发现排序是一致的。那      么“SORT ORDER BY STOPKEY”方式的排序是否是一致的呢？我们将语句6执行10次同样发现，其结果是一致的。那么为什么我们用语句6查第1到10条记录和11到20条记录时，有些记录为什么在这两个查询中出现的名次并不一致呢。

 

--语句7

select *

  from (select a.* from t_testrownum a order by rvalue)

 where rownum <= 20 ;

 

--语句8

select *

  from (select a.* from t_testrownum a order by rvalue)

 where rownum <= 10 ;

为了找出排序不一致的原因，我们分别执行语句7和语句8，这时你会发现，前10名的记录在两次查询中并不一样。为此，我们得出结论，当stopkey不同时，排序结果是不同的。为什么会这样呢，大师TOM的解释是“SORT ORDER BY STOPKEY”是Oracle为优化TOPN（查询排序后的前N条记录）查询采用的一种算法。大致的思想是：先取出为排序时前面的N条记录，对这N条记录排序，然后用后面的剩下的所有记录依排序要求插入前N条记录中。一般来说，这样的插入排序也应该是稳定的，那为什么N不同，排序结果就不一样呢？下面的两条查询语句似乎可以给你一点启发：

 

--语句9

select a.*, rownum r from t_testrownum a where rownum <= 10 orderby rvalue;

 

--语句10

select a.*, rownum r from t_testrownum a whererownum <= 20 orderby rvalue;

它们的执行计划如下：

SELECT STATEMENT, GOAL = CHOOSE

 SORT ORDER BY

  COUNT STOPKEY

   TABLE ACCESS FULL   Object owner=USDPD502   Object name=T_TESTROWNUM

从上面两条查询语句的结果我们可以看出，排在前面的10记录也不是一致的。要注意的是，这两条语句的执行计划中并没有使用“SORT ORDER BY STOPKEY”算法。而是普通的排序“SORT ORDER BY”。只是这两次排序的记录条数不一样，这时有些人可能会怀疑是在排序前两次查询输出记录的顺序就是不一样的。我们可以这么测试一下，先对t_testrownum表的所有20条记录排序，然后从表中删除掉后10条记录，从语句9和语句10中删除掉where rownum <= N条件，我们发现，查询结果和语句9和语句10是一样的。由此，我们可以得出结论，当参与排序的记录数量不同时，具有相同值的记录的排序顺序是不同的。

进行分页查询时，如果同一条记录在多个分页中出现，这样的结果肯定不是你所期望的。为了避免这种现象的发生，一个简单的方法就是在排序条件中增加辅助排序字段，使得每条记录的组合排序字段是唯一的。

如何在分页查询中避免排序

对于需要排序的分页查询来说，如果参与排序的结果集很大，而实际返回的记录数很少，那么有两点是需要注意的：第一大结果集排序对系统资源的占用，第二如果排序字段的值不唯一，某些记录会出现在多个分页中。如何避免以上的两点呢，我们知道，索引的键值是有序组织的，我们是否可以利用索引来避免排序呢。答案是肯定的，我们在rvalue上建立索引：

 

--语句11

create index idx_testrownum_rvalue on t_testrownum(rvalue);

这时，我们把语句6修改一下：

 

--语句12

select *

  from (select b.*, rownum r

          from (select a.*

                  from t_testrownum a

                 where rvalue > chr(1)

                 order by rvalue) b

         where rownum <= 20)

 where r >= 11 ;

执行语句12，它的执行计划如下：

SELECT STATEMENT, GOAL = CHOOSE

VIEW   Object owner=USDPD502  

COUNT STOPKEY

   VIEW    Object owner=USDPD502   

     TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM

       INDEX RANGE SCAN Object owner=USDPD502 Object name=IDX_TESTROWNUM_RVALUE

从上面的执行计划中，我们已看不到“SORT ORDER BY STOPKEY”字样，说明没有排序步骤。那么记录在分页中重复的问题是否也解决了呢，经过测试，该问题也不复存在。

那么如果我们需要降序排序呢? 对于降序排序，我们需要增加相应的hints来提示优化器走降序索引扫描。

 

--语句13

select *

  from (select b.*, rownum r

          from (select /*+index_desc(a idx_testrownum_rvalue)*/

                 a.*

                  from t_testrownum a

                 where rvalue > chr(1)

                 order by rvalue desc) b

         where rownum <= 20)

 where r >= 11;

语句13的执行计划如下：

SELECT STATEMENT, GOAL = CHOOSE   

 VIEW Object owner=USDPD502  

  COUNT STOPKEY     

   VIEW Object owner=USDPD502  

    TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM 

     INDEXRANGE SCAN DESCENDING Objectowner=USDPD502

利用索引避免排序需要的注意点

虽然使用索引来避免排序是一个好方法，但是，任何事物都不可能是十全十美的，使用该方法时需要注意以下几点：

1)排序字段上的索引必须是升序索引，如果使用降序索引将导致升序排序时分页出现问题。（具体是什么原因我现在还没弄明白，如果有知道原因的可以指点一下）

2)在分页开始记录数大于10000后，利用索引排序进行分页的性能反而不如直接排序分页的方式好。（如果字段是数字性，性能下降不大，如果是字符串则性能下降明显，这可能和字符串和数字的比较方式不同有关）

 

 

 

 

 

其他数据库分页查询：

 

1.oracle数据库分页

select * from (select a.*,rownum rc from 表名 where rownum<=endrow) a where a.rc>=startrow

2.DB2数据库分页

Select * from (select rownumber() over() as rc,a.* from (select * from 表名 order by 列名) as a) where rc between startrow and endrow

3.SQL Server 2000数据库分页

Select top pagesize * from 表名 where 列名 not  in(select top pagesize*page 列名 from  表名 order by 列名) order by 列名

4.SQL Server 2005数据库分页

Select * from (select 列名,row_number() over(order by  列名1) as 别名from 表名) as t where t.列名1>=startrow and t.列名1<=endrow

5.MySQL数据库分页

Select * from 表名 limit startrow,pagesize

(Pagesize为每页显示的记录条数)

6.PostgreSQL数据库分页

Select * from 表名 limit pagesize,offset startrow

(Pagesize为每页显示的记录条数.)