oracle 高效分页查询SQL

     因为一个功能需要读取一个大表的所有数据做业务处理，那这样肯定不能一次性查出所有数据，需要程序分页查询处理，模拟测试一个200万数据量的表发现耗时很久，并不是业务处理耗时，而且分页查询耗时了。oracle的分页查询可能大家都知道利用rownum,而且大部分公司这种分页都是底层封装好的了，所有平时大家使用的时候也没注意（这次之后特意留意了一下，我们公司就是用了错误的）

1. select a.* from ( select t.*,rownum rowno from test t where rownum <= 20 ) a where a.rowno >= 11;

2.select a.* from ( select t.*,rownum rowno from test t ) a where a.rowno >= 11 and a.rowno <= 20;

这两条查询语句看着区别不大，但是性能却差很多。经过测试第一种性能最好，而且随着数量的增大，几乎不受影响。第二种随着数据量的增大，查询速度也越来越慢。表200W条数据的情况下，第一种查询耗时基本是0.3s,第二种基本在1.3s以上。一个查询足足差了一秒。别小看这1秒。200W条数据每次查询1000条，查询完也差了2000s=33分钟.
分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 20这句上。

正确的查询语句

• 无order by （效率最高）

select a.* from ( select t.*,rownum rowno from test t where rownum <= 20 ) a where a.rowno >= 11;

• 有 order by （数据量越大效率越低）

select *
    from (select a.*, rownum rowno
            from (select t.*
                    from test t
                   order by t.create_date desc) a
           where rownum <= 20) b
   where b.rowno >= 11;

错误似乎又很常用的写法

• 无order by （数据量越大效率越低）

select a.* from ( select t.*,rownum rowno from test t ) a where a.rowno >= 11 and a.rowno <= 20;

• 有 order by （数据量越大效率越低）

 select *
    from (select a.*, rownum rowno
            from (select t.*
                    from test  t
                   order by t.create_date desc) a ) b
   where b.rowno >= 11 and b.rowno <= 20;

原因

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。
对于正确有order by语句，第二层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了
对于错误有order by 语句，由于查询条件where b.rowno >= 11 and b.rowno <= 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道b.rowno代表什么）。因此对于这个语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

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