oracle分页查询sql语法-最高效的分页

1、无order by排序的写法。（效率最高）

--(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM t_obs_order t
         WHERE bat_code = '131008000000001811'
           AND ROWNUM <= 20) table_alias
 WHERE table_alias.rowno >= 10;

2、有ORDER BY排序的写法。(效率最高)
--(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)

SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (SELECT t.*
                  FROM t_obs_order t
                 WHERE bat_code = '131008000000001811'
                 ORDER BY order_code DESC) tt
         WHERE ROWNUM <= 20) table_alias
 WHERE table_alias.rowno >= 10;

3、无ORDER BY排序的写法。(建议使用方法1代替)
--(此方法随着查询数据量的扩张，速度会越来越慢哦！)

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM t_obs_order t
         WHERE bat_code = '131008000000001811') table_alias
 WHERE table_alias.rowno <= 20
   AND table_alias.rowno >= 10;

4、有ORDER BY排序的写法.(建议使用方法2代替)
--(此方法随着查询范围的扩大，速度会越来越慢哦！)

SELECT *
   FROM (SELECT tt.*, ROWNUM AS rowno
           FROM (SELECT *
                   FROM t_obs_order t
                  WHERE bat_code = '131008000000001811'
                  ORDER BY order_code DESC) tt) table_alias
  WHERE table_alias.rowno BETWEEN 10 AND 20;

5、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 AND40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

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

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