（Oracle）SQL优化技巧（一）：分页查询
目录
分页查询框架
分页查询注意事项
有序/无序分页
事务带来的影响
分页查询与索引
排序字段索引实验
组合索引实验
利用ROWNUM进行分页查询的方法在各版本都是适用的，11g,12c,19c都可以使用该方法哦。在分享分页查询方法之前，需要先聊下ROWNUM，这玩意儿要是聊不清楚，那这个方法就不太好理解。
- 伪列
ROWNUM是Oracle的一个伪列，并不真实存在于表结构中。
- 行号
ROWNUM作用记录是返回结果集中的每一行的行号，是在查询结果返回之后才计算的。
在了解ROWNUM以上两个特性之后，可以开始分享根据ROWNUM进行的分页查询方法了。
分页查询框架
SELECT *
FROM(SELECT * FROM (SELECT sp.*,ROWNUM rnFROM (/*需要分页的SQL*/) sp)WHERE ROWNUM <= x)
WHERE rn >=y在上述代码中
x表示查询的结束行
y表示查询的起始行
分页查询注意事项
有序/无序分页
Attention Please！！！
下面将是一大段文字描述，因为我在刚接触分页查询的时候吃了不少亏，也有许多不理解的地方，现在我把我的浅薄理解写出来，供大家参考！
如果您时间宝贵与紧张，可以不看下面的描述，只需要记住一点：
进行分页查询优化的目标SQL需要根据实际场景看是否进行排序！
分页查询，顾名思义即为将表中的数据分成若干页，且指定每页行数进行展示；目的就是为了避免目标表中的数据量太大，而一次性查询全部引起的查询效率低下。大家可以想象一下，我们在阅读一本新书的时候，是用什么样的方式阅读呢？正常人肯定是从第1页开始，一页一页的往后按照顺序进行阅读。Oracle如果拟人化肯定也是个正常人，因为它做的一切都是合乎理性的；它也会从第一页开始按照顺序往后阅读。
那么重点就来了：“顺序”。
在做分页查询的时候，是需要保证进行分页查询的目标SQL要有一个合理的排序。前文已经叙述过ROWNUM是在查询返回后计算的一个行号，如果查询的结果集本身是排序是混乱的，那么具体每页展示的数据就不会是我们期待的一个结果。
用我们在学习Oracle时的一个老朋友scott用户举个例子，scott用户下有张EMP表，表里有各个职员的薪水。在对 “SELECT * FROM EMP” 这个SQL进行分页查询优化时，如果按照薪水从高到低的需要去查看这些数据，那么理想分页情况就应该是第1页展示薪水排前N名的职工信息，第2页展示薪水排第N+1~2N名的职工信息，以此类推。但如果不对salary字段进行降序查询的话，是达不到期待效果的。
例如要查询公司薪水排名6~10的员工信息，以scott.emp表为例子进行查询，那么分页查询SQL代码如下。
SELECT *
FROM(SELECT * FROM (SELECT sp.*,ROWNUM rnFROM (SELECT * FROM emp ORDER BY sal DESC) sp)WHERE ROWNUM <= 10)
WHERE rn >=6当然了，如果您觉得无序分页对您的查询没有什么影响的话，也就没有必要进行排序查询了；这个肯定还是要根据实际场景来决定。
事务带来的影响
想象一下，您在阅读的是一本电子书，您已经阅读完当前页了，就开始往后翻，但是这个叼电子书系统突然抽风把您已经阅读过的前面页数的内容更改了，这个时候您读到的信息就不一定是准确的了。
Oracle也一样，可能每时每刻都在发生着事务；这些事务都会对正在进行分页查询的SQL结果集造成影响，所以在进行分页查询时需要考虑数据的一致性。有些分页查询的场景是不需要考虑事务带来的数据变化；但有的场景是需要的，就比如说做ETL的，在同步数据到数据仓库的时候，就需要考虑这些事务带来的影响。
分页查询与索引
这里所指的分页查询是有序分页。
如果您的查询SQL有进行排序的话，那么需要在进行排序的字段上建立索引哦。为什么呢？因为索引是已经进行过排序的，可以利用索引的这个特性来进一步优化分页语句。
下面做个小实验哦。（我下面对实验分个三级标题哦，可以让整篇文章看着更清晰些。同时也感觉我的排版能力菜的一批！）
排序字段索引实验
- 先建立一张测试表
create table HR.spage_0406 as select * from dba_objects
- 进行分析查询改写
SELECT *
FROM(SELECT * FROM (SELECT sp.*,ROWNUM rnFROM (select * from HR.spage_0406 order by object_id) sp)WHERE ROWNUM <= 10)
WHERE rn >=1- 查看当前分页查询执行计划
可以发现现在走的是全表扫描，且A-ROWS是72695
SQL_ID 9xkcnduur1d6p, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 10) WHERE RN >=1Plan hash value: 2601037360-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2541 (100)| | 10 |00:00:00.07 | 1416 | | | |
|* 1 | VIEW | | 1 | 10 | 4940 | | 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | | | |
|* 2 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:00.07 | 1416 | | | |
| 3 | VIEW | | 1 | 72695 | 34M| | 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | | | |
| 4 | COUNT | | 1 | | | | | | 10 |00:00:00.07 | 1416 | | | |
| 5 | VIEW | | 1 | 72695 | 33M| | 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | | | |
| 6 | SORT ORDER BY | | 1 | 72695 | 9370K| 13M| 2541 (1)| 00:00:01 | 10 |00:00:00.07 | 1416 | 14M| 1431K| 12M (0)|
| 7 | TABLE ACCESS FULL| SPAGE_0406 | 1 | 72695 | 9370K| | 395 (1)| 00:00:01 | 72695 |00:00:00.01 | 1416 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------- 给排序字段添加索引
create index IDX_SPAGE_OBJECTID on HR.spage_0406(object_id,0)- 再次查看分页查询执行计划
可以发现现在走的是索引全扫描，且A-ROWS是10。现在这张表还是不够大，还是体现不出来这种优化方式的优势，越大的表越能实际感受的到它的优势。
SQL_ID 9xkcnduur1d6p, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 10) WHERE RN >=1Plan hash value: 1210249890-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1762 (100)| | 10 |00:00:00.01 | 3 |
|* 1 | VIEW | | 1 | 10 | 4940 | 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 72695 | 34M| 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 4 | COUNT | | 1 | | | | | 10 |00:00:00.01 | 3 |
| 5 | VIEW | | 1 | 72695 | 33M| 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID| SPAGE_0406 | 1 | 72695 | 9370K| 1762 (1)| 00:00:01 | 10 |00:00:00.01 | 3 |
| 7 | INDEX FULL SCAN | IDX_SPAGE_OBJECTID | 1 | 72695 | | 182 (0)| 00:00:01 | 10 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------------------------------组合索引实验
那么分页查询与索引的故事到这里就结束了吗？当然不是啦，还有还有呢。请大家耐心看下面的叙述哦！
上面的查询是没有谓词过滤的，也就是WHERE条件。如果查询中有谓词条件，大家是可以考虑创建联合索引；将谓词字段与排序字段放在一起创建组合索引，且尽量将排序字段作为组合索引的前导列，也就是创建组合索引时的一个字段。
例如下面这个分页查询，加进了谓词过滤，执行计划立马就变差了。
SELECT *
FROM(SELECT * FROM (SELECT sp.*,ROWNUM rnFROM (select * from HR.spage_0406 where owner='SYS' order by object_id) sp)WHERE ROWNUM <= 10)
WHERE rn >=1SQL_ID 67hjvw5r90c9g, child number 1
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 WHERE OWNER='SYS' ORDER BY OBJECT_ID) SP) WHERE ROWNUM <=
10) WHERE RN >=1Plan hash value: 2601037360-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 483 (100)| | 10 |00:00:00.04 | 1416 | | | |
|* 1 | VIEW | | 1 | 10 | 4940 | | 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | | | |
|* 2 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:00.04 | 1416 | | | |
| 3 | VIEW | | 1 | 2908 | 1402K| | 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | | | |
| 4 | COUNT | | 1 | | | | | | 10 |00:00:00.04 | 1416 | | | |
| 5 | VIEW | | 1 | 2908 | 1365K| | 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | | | |
| 6 | SORT ORDER BY | | 1 | 2908 | 374K| 552K| 483 (1)| 00:00:01 | 10 |00:00:00.04 | 1416 | 10M| 1258K| 9559K (0)|
|* 7 | TABLE ACCESS FULL| SPAGE_0406 | 1 | 2908 | 374K| | 394 (1)| 00:00:01 | 52493 |00:00:00.01 | 1416 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------那么我们可以创建组合索引，代码如下：
create index IDX_SPAGE_OWID on HR.spage_0406(object_id,owner)
然后再看该分页查询的执行计划，欸，变好了！
SQL_ID 67hjvw5r90c9g, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM
HR.SPAGE_0406 WHERE OWNER='SYS' ORDER BY OBJECT_ID) SP) WHERE ROWNUM <=
10) WHERE RN >=1Plan hash value: 961832651------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 275 (100)| | 10 |00:00:00.01 | 3 | 1 |
|* 1 | VIEW | | 1 | 10 | 4940 | 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.01 | 3 | 1 |
| 3 | VIEW | | 1 | 2908 | 1402K| 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
| 4 | COUNT | | 1 | | | | | 10 |00:00:00.01 | 3 | 1 |
| 5 | VIEW | | 1 | 2908 | 1365K| 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| SPAGE_0406 | 1 | 2908 | 374K| 275 (1)| 00:00:01 | 10 |00:00:00.01 | 3 | 1 |
|* 7 | INDEX FULL SCAN | IDX_SPAGE_OWID | 1 | 2908 | | 211 (1)| 00:00:01 | 10 |00:00:00.01 | 2 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------------------上面罗里吧嗦了一堆内容，但对于有序分页查询来讲，还有下面两点内容需要提醒大家：
1、索引字段或组合索引先导列字段中的值能否最大程度最充分的完成排序；
2、索引的排序方式和SQL的实际排序方式是否一致，别一个是升序一个是降序；
扯些闲话：
最后我给自己再留一个作业吧，上面所有的叙述其实都是讲的单表分页查询；多表分页查询和单表分页查询的框架是一致的，但再进一步优化上是有区别的。今天受限于时间，下次我再对多表关联分页查询进行分享，或者有时间了我直接在本篇文章基础上进行编辑了。
最后就是，明天清明假期就结束了，要开始上班了，但我不想上班💔💔💔