从Oracle 12C开始，提供了一种行限制（row limit）的写法，这种写法也可以用于分页查询。在介绍新写法之前，先介绍一下Oracle 12C之前的分页查询的写法，分析一下各种写法的性能差异。
先介绍一下Oracle 12C中行限制的新写法，顺便看一下本文中用作演示的示例数据，emp表的数据总共有100万行。
select * from emp order by sal fetch first 5 rows only;
EMPNO ENAME MGR HIREDATE SAL COM DEPTNO
---------- -------------------------------------------------- ---------- --------- ---------- ---------- ----------
92 Bryana Worswick 80 03-JUL-18 2504 1 10
99 Rufus McDermott-Row 63 01-MAR-02 2512 61 10
36 Jacenta Joust 71 31-JAN-05 2515 9 30
2 Blisse Adamoli 65 12-APR-14 2517 67 20
98 Simona Shropshire 52 16-AUG-08 2518 24 88
1 使用子查询实现分页
1.1 不需要排序
select a.EMPNO,ENAME,SAL from
(select empno,ename,sal,rownum rowno from emp ) a where a.rowno >= 11 and a.rowno <= 15;
EMPNO ENAME SAL
---------- -------------------------------------------------- ----------
75 Shell Whatman 5059
52 Elisabet Gristock 4419
11 Dag Quakley 4303
6 Zebedee Pesselt 4618
4 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"<=15 AND "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 order by SAL) a where a.rowno >= 11 and a.rowno <= 15;
EMPNO ENAME SAL
---------- -------------------------------------------------- ----------
4 Anthe Noar 3198
11 Dag Quakley 4303
52 Elisabet Gristock 4419
6 Zebedee Pesselt 4618
75 Shell Whatman 5059
这种写法错误的原因是取得的行号实际是对emp按照SAL列排序之前的数据，根据这个错误的行号取得的数据自然也是错误的，要想获得正确的行号，就需要加一层子查询，将语句写成下面这样
select b.EMPNO,b.ENAME,b.SAL from
(select rownum ROWNO, a.* from (select empno,ename,sal from emp order by SAL) a where rownum<=15) b where b.rowno >= 11; 2
EMPNO ENAME SAL
---------- -------------------------------------------------- ----------
71 Meridith Stanbrooke 2526
61 Vin Tuft 2526
14 Alyss Concannon 2526
9 Rocky Ravens 2526
98 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 ORDER BY | | 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"<=15 AND "B"."ROWNO">=11))
从上面的执行计划来看，Oracle在执行查询转换时未能将查询条件下推到排序操作，对全表扫描到的100万行数据进行了排序，这时因为Oracle只能将条件下推到下一层的子查询，不能向下推到第二层的子查询当中。将语句改一下，将查询页面的上限写到子查询b内
select b.EMPNO,b.ENAME,b.SAL from
(select rownum ROWNO, a.* from (select empno,ename,sal from emp order by 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 ORDER BY 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(order by sal) rownumber, e.*
from emp e)
where rownumber >= 11 and 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">=11 AND "ROWNUMBER"<=15))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "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"<=15 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=15)
这条语句无需看实际执行计划，就可以看出来只扫描了15行数据，从WINDOW NOSORT STOPKEY来看，扫描完了符合条件的数据之后即刻停止全表扫描。
3.2 有序查询
有序查询写法也非常简单，在语句中加入排序即可
select EMPNO,ENAME,SAL from emp order by 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"<=15 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">10))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "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 (
ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber"
FROM "TEST"."EMP" "EMP" WHERE 10<15) "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=15 AND "from$_subquery$_002"."rowlimit_$$_rownumber">10
ORDER BY "from$_subquery$_002"."rowlimit_$_0"
经过优化器转换后，语句看起来比之前复杂多了，但是可以看到，转换后的语句使用分析函数，除了名字有些差异之外，同之前的用分析函数实现排序分页查询的语句相同。Oracle用另一种语法进行了包装，使我们写起来更容易，也更好理解。
最后修改时间：2023-03-14 17:06:32
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
