最近遇到了这个问题，整理下Oracle 分页查询的高效写法

一、 低效与高效写法对比

低效的写法

select column_lists from 
 (select rownum as rn,A.* from 
    (select column_lists from table_name where col_1=:b0 order by col_2) A 
 ) where rn<=:b2 and rn>:b1;

高效的写法

select column_lists from
 (select rownum as rn,A.* from
  (select column_lists from table_name where col_1=:b0 order by col_2) A
 where rownum<=:b2
 ) where rn>:b1;

       低效写法需要将内层的结果集全部排序，再从中取出需要的部分；而高效写法只需要获取排序后<=:b2部分的结果就可以了。一般分页查询访问前面部分页面的几率较大，内层查询的结果集越大，性能差距越明显。如果是访问分页的最后部分的页面，基本上就没什么差别了。

       建议创建col_1和col_2字段上的联合索引，避免排序，提高效率。联合索引的字段顺序不能错，order by的字段要放在联合索引的最后。

二、 Oracle通用分页格式

1. 无order by

没有order by语句的分页，比有order by少一层

SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
          FROM DONORINFO t
          WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
          AND TO_DATE ('20060731', 'yyyymmdd')
          AND ROWNUM <= page*size) table_alias
WHERE table_alias.rowno > (page-1)*size;

2. 有order by

SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
           FROM(SELECT * FROM DONORINFO t
                    WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
                    AND TO_DATE ('20060731', 'yyyymmdd')
                    ORDER BY t.BIRTHDAY desc
                   ) r
           where ROWNUM <= page*size 
          ) table_alias
WHERE table_alias.rowno > (page-1)*size;

3. 分析函数

也可以使用row_number() over函数，但是并没有什么性能优势。

select * 
from(select d.*,row_number() over(order by d.BIRTHDAY) as rownumber 
       from DONORINFO d
       WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
        AND TO_DATE ('20060731', 'yyyymmdd')
 ) p 
where p.rownumber BETWEEN size*(page-1)+1 AND page*size;

       如果是order by desc，正常情况优化器会自动使用index descending扫描方式，不需要建索引的时候加desc 。也有sql复杂的时候优化器没有使用index descending扫描方式，可以用index_desc来纠正。

select * from
(select /*+ index_desc(d IDX_BIRTHDAY) */ d.*,row_number() over(order by d.BIRTHDAY desc) as rownumber 
       from DONORINFO d
       WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
        AND TO_DATE ('20060731', 'yyyymmdd')
 ) p 
where p.rownumber BETWEEN size*(page-1)+1 AND page*size;

4. 12c 的offset写法

       oracle 12c 中使用了简洁的offset 语法，本质是使用分析函数row_number()在内部做了改写，效率也很高。不过当前的主流写法还是上面使用rownum伪列的方法。

select /*+ index_desc(d IDX_BIRTHDAY) */ d.*
from DONORINFO d
WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd')
ORDER BY t.BIRTHDAY desc
offset 10 rows fetch next 10 rows only;

三、 分页使用误区

分页不适合用来做大结果集数据分片， 问题：

  • 返回记录数多，使用索引效率低，需要多次全表扫描
  • 分页值越大，需要获取的记录数越多，效率越低
  • 不能并发执行，表数据实时变化，会出现重复取数或漏取的问题

下面是一个典型的例子，都到百万行了还分页，效率很低

select column_lists from
(select rownum as rn,A.* from
 (select column_lists from table_name where col_1=:b0 order by col_2) A
where rownum<=1500000
) where rn>1200000;

参考

rownum应用系列之分页查询

rownum应用系列之分页查询--续1

oracle高效分页查询总结 - 风吹过的绿洲 -