从Oracle 12C开始，提供了一种行限制（row limit）的写法，这种写法也可以用于分页查询。在介绍新写法之前，先介绍一下Oracle 12C之前的分页查询的写法，分析一下各种写法的性能差异。
先介绍一下Oracle 12C中行限制的新写法，顺便看一下本文中用作演示的示例数据，emp表的数据总共有100万行。
select*from emp orderby sal fetch first 5 rows only; EMPNO ENAME MGR HIREDATE SAL COM DEPTNO ---------- -------------------------------------------------- ---------- --------- ---------- ---------- ----------92 Bryana Worswick 8003-JUL-18250411099 Rufus McDermott-Row 6301-MAR-022512611036 Jacenta Joust 7131-JAN-0525159302 Blisse Adamoli 6512-APR-142517672098 Simona Shropshire 5216-AUG-0825182488
1 使用子查询实现分页
1.1 不需要排序
select a.EMPNO,ENAME,SAL from(select empno,ename,sal,rownum rowno from emp ) a where a.rowno>=11and a.rowno<=15; EMPNO ENAME SAL ---------- -------------------------------------------------- ----------75 Shell Whatman 505952 Elisabet Gristock 441911 Dag Quakley 43036 Zebedee Pesselt 46184 Anthe Noar 3198
上面的写法返回的结果是正确的，但是性能较差，看一下它的执行计划
----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|----------------------------------------------------------------------------|0|SELECT STATEMENT ||1000K|62M|4956(1)|00:00:01||*1| VIEW ||1000K|62M|4956(1)|00:00:01||2|COUNT|||||||3|TABLE ACCESS FULL| EMP |1000K|55M|4956(1)|00:00:01|---------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------1- filter("A"."ROWNO"<=15AND"A"."ROWNO">=11)
从上面的执行计划可以看出，在查询时做了全表扫面，总共扫描了1百万行，上面的语句可以优化一下，改成下面的写法
select a.EMPNO,ENAME,SAL from(select empno,ename,sal,rownum rowno from emp where rownum <=15) a where a.rowno>=11;
上面的语句把条件查询的上限放到了子查询里面，执行计划也发生了变化
--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|----------------------------------------------------------------------------|0|SELECT STATEMENT ||15|990|2(0)|00:00:01||*1| VIEW ||15|990|2(0)|00:00:01||*2|COUNT STOPKEY |||||||3|TABLE ACCESS FULL| EMP |15|870|2(0)|00:00:01|---------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------1- filter("A"."ROWNO">=11)2- filter(ROWNUM<=15)
语句的执行计划发生了改变，执行计划中第二个操作是COUNT STOPKEY操作，Oracle在扫描完满足过滤条件的行之后结束了全表扫描，总共只扫描了5数据，执行的性能比前一条语句好了不少。当然，随着向后翻页，语句的性能会逐渐变差，比如翻页到900011行时的执行计划
----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|----------------------------------------------------------------------------|0|SELECT STATEMENT ||900K|56M|4461(1)|00:00:01||*1| VIEW ||900K|56M|4461(1)|00:00:01||*2|COUNT STOPKEY |||||||3|TABLE ACCESS FULL| EMP |900K|49M|4461(1)|00:00:01|----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1- filter("A"."ROWNO">=900011)2- filter(ROWNUM<=900015)
总共扫描了90万行，不过还是比前面一种写法少扫了10万行，也就是说，这种写法的性能最差时同前面一种写法相同，大多数情况下要比前一种写法性能好很多。
1.2 需要排序
如果分析查询的结果是需要经过排序的，使用子查询来实现分页就要复杂一点，下面的写法是错误的，返回的是错误的结果
select a.EMPNO,ENAME,SAL from(select empno,ename,sal,rownum rowno from emp orderby SAL) a where a.rowno>=11and a.rowno<=15; EMPNO ENAME SAL ---------- -------------------------------------------------- ----------4 Anthe Noar 319811 Dag Quakley 430352 Elisabet Gristock 44196 Zebedee Pesselt 461875 Shell Whatman 5059
这种写法错误的原因是取得的行号实际是对emp按照SAL列排序之前的数据，根据这个错误的行号取得的数据自然也是错误的，要想获得正确的行号，就需要加一层子查询，将语句写成下面这样
select b.EMPNO,b.ENAME,b.SALfrom(select rownum ROWNO, a.*from(select empno,ename,sal from emp orderby SAL) a where rownum<=15) b where b.rowno>=11; EMPNO ENAME SAL ---------- -------------------------------------------------- ----------71 Meridith Stanbrooke 252661 Vin Tuft 252614 Alyss Concannon 25269 Rocky Ravens 252698 Madelyn Norewood 2526
这时，语句的实际执行计划如下所示
-------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | OMem |1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||5|00:00:00.21|18268|||||*1| VIEW ||1|1000K|5|00:00:00.21|18268|||||2|COUNT||1||1000K|00:00:00.82|18268|||||3| VIEW ||1|1000K|1000K|00:00:00.80|18268|||||4| SORT ORDERBY||1|1000K|1000K|00:00:00.79|18268|84M|3151K|74M (0)||5|TABLE ACCESS FULL| EMP |1|1000K|1000K|00:00:00.04|18268||||Predicate Information (identified by operation id):---------------------------------------------------1- filter(("B"."ROWNO"<=15AND"B"."ROWNO">=11))
从上面的执行计划来看，Oracle在执行查询转换时未能将查询条件下推到排序操作，对全表扫描到的100万行数据进行了排序，这时因为Oracle只能将条件下推到下一层的子查询，不能向下推到第二层的子查询当中。将语句改一下，将查询页面的上限写到子查询b内
select b.EMPNO,b.ENAME,b.SALfrom(select rownum ROWNO, a.*from(select empno,ename,sal from emp orderby SAL) a where rownum<=15) b where b.rowno>=11;
改写后的语句执行结果时正确的，查询的结果同改写前相同，看一下这个语句的实际执行计划
----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | OMem |1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||5|00:00:00.13|18267|||||*1| VIEW ||1|15|5|00:00:00.13|18267|||||*2|COUNT STOPKEY ||1||15|00:00:00.13|18267|||||3| VIEW ||1|1000K|15|00:00:00.13|18267|||||*4| SORT ORDERBY STOPKEY||1|1000K|15|00:00:00.13|18267|2048|2048|2048(0)||5|TABLE ACCESS FULL | EMP |1|1000K|1000K|00:00:00.04|18267|||| Predicate Information (identified by operation id):---------------------------------------------------1- filter("B"."ROWNO">=11)2- filter(ROWNUM<=15)4- filter(ROWNUM<=15)
从语句的实际执行计划来看，虽然Oracle优化器在评估时排序操作返回的数据是1百万行，但是实际只排序了15行就结束了排序操作，语句执行的时间和改写之前相比减少了很多，使用的内存和改写之前相比也减少了很多。
2 使用分析函数实现分页查询
上面的有序分析查询的语句用了2层子查询嵌套，看起来比较复杂，有没有看起来比较简单一点的写法？看起来简单的写法还是有的，用分析函数也可以实现有序分页查询，其实现的原理是先用分析函数排序，取得排序后的行号，根据行号查询所需要的页。上面的语句可以用分析函数改写一下：
select EMPNO,ENAME,SAL from(select row_number() over(orderby sal) rownumber, e.*from emp e)where rownumber >=11and rownumber <=15;
改写后的语句的执行结果也是正确的，实际执行计划如下
----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | OMem |1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||5|00:00:00.15|18267|||||*1| VIEW ||1|15|5|00:00:00.15|18267|||||*2| WINDOW SORT PUSHED RANK||1|1000K|15|00:00:00.15|18267|2048|2048|2048(0)||3|TABLE ACCESS FULL | EMP |1|1000K|1000K|00:00:00.04|18267||||----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1- filter(("ROWNUMBER">=11AND"ROWNUMBER"<=15))2- filter(ROW_NUMBER() OVER (ORDERBY"SAL")<=15)
从这条语句的实际执行计划来看，WINDOW SORT PUSHED RANK也是执行了15行之后就停止了操作，由于减少了子查询，实际的执行事件比子查询时还要少些，性能要稍好一点。
3 使用offset实现分页查询
上面的两种写法，一种使用了2层子查询，一种使用了分析函数，2层子查询看起来比较复杂，分析函数理解起来也有一定的难度，有没有写起来简单，理解起来也比较容易的写法，就像MySQL那样。Oracle 从12C之后为select 语句提供了offset fetch的写法，也可以用来分页查询
3.1 无序查询
用offset实现无序的分页查询，语句如下：
select EMPNO,ENAME,SAL from emp offset 10 rows fetch next 5 rows only;
这条语句很容易理解，offset设置的偏移量，fecth next是取偏移量之后的5条数据，语句的执行结果是正确的，执行计划如下：
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|-------------------------------------------------------------------------------|0|SELECT STATEMENT ||15|990|2(0)|00:00:01||*1| VIEW ||15|990|2(0)|00:00:01||*2| WINDOW NOSORT STOPKEY||15|870|2(0)|00:00:01||3|TABLE ACCESS FULL | EMP |15|870|2(0)|00:00:01|-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1- filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=15AND"from$_subquery$_002"."rowlimit_$$_rownumber">10)2- filter(ROW_NUMBER() OVER (ORDERBYNULL)<=15)
这条语句无需看实际执行计划，就可以看出来只扫描了15行数据，从WINDOW NOSORT STOPKEY来看，扫描完了符合条件的数据之后即刻停止全表扫描。
3.2 有序查询
有序查询写法也非常简单，在语句中加入排序即可
select EMPNO,ENAME,SAL from emp orderby sal offset 10 rows fetch next 5 rows only;
上面的语句执行结果是正确的，执行计划如下：
----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | OMem |1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1||5|00:00:00.15|18267|||||*1| VIEW ||1|15|5|00:00:00.15|18267|||||*2| WINDOW SORT PUSHED RANK||1|1000K|15|00:00:00.15|18267|2048|2048|2048(0)||3|TABLE ACCESS FULL | EMP |1|1000K|1000K|00:00:00.23|18267||||----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1- filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=15AND"from$_subquery$_002"."rowlimit_$$_rownumber">10))2- filter(ROW_NUMBER() OVER (ORDERBY"SAL")<=15)
这个执行计划看起来有点眼熟？没错，这个执行计划同使用分析函数的执行计划相同。但是语句看起来简单多了，也很容易理解。
4 分页查询新写法的原理
从执行计划来看，Oracle 12C之后的新写法也是利用了分析函数来实现的，具体是如何实现的可以利用10053事件从语句执行计划生成的过程中看到。这里不再展示如何开启10053事件，只是将语句经过查询转后的最终结果截取出来
SELECT"from$_subquery$_002"."EMPNO""EMPNO","from$_subquery$_002"."ENAME""ENAME","from$_subquery$_002"."SAL""SAL"FROM(SELECT"EMP"."EMPNO""EMPNO","EMP"."ENAME""ENAME","EMP"."SAL""SAL","EMP"."SAL""rowlimit_$_0", ROW_NUMBER() OVER (ORDERBY"EMP"."SAL")"rowlimit_$$_rownumber"FROM"TEST"."EMP""EMP"WHERE10<15)"from$_subquery$_002"WHERE"from$_subquery$_002"."rowlimit_$$_rownumber"<=15AND"from$_subquery$_002"."rowlimit_$$_rownumber">10ORDERBY"from$_subquery$_002"."rowlimit_$_0"
经过优化器转换后，语句看起来比之前复杂多了，但是可以看到，转换后的语句使用分析函数，除了名字有些差异之外，同之前的用分析函数实现排序分页查询的语句相同。Oracle用另一种语法进行了包装，使我们写起来更容易，也更好理解。
