挖出一些分页查询的秘密

今天看下分页查询这个知识点，能挖出什么样的知识。

测试表中符合条件的记录共32条，如果需要得到第10到第20条的记录，我们能怎么做？

SQL> select rownum, employee_id
  2  from hr.employees a
  3  where hire_date between to_date('20050101','yyyymmdd') 
  4                      and to_date('20060201','yyyymmdd');


    ROWNUM EMPLOYEE_ID
---------- -----------
         1         202
         2         101
         3         103
         4         105
         5         110
         6         111
         7         116
         8         117
         9         121
        10         123
        11         125
        12         129
        13         130
        14         131
        15         138
        16         142
        17         146
        18         147
        19         150
        20         151
        21         152
        22         159
        23         160
        24         162
        25         168
        26         170
        27         175
        28         180
        29         185
        30         188
        31         189
        32         193


32 rows selected.

这个需求用到的，其实就是分页，一般有两种基本格式。

格式1，

SQL> select * 
  2  from (select rownum as rowno, a.employee_id as id
  3        from (select employee_id from hr.employees 
  4              where hire_date between to_date('20050101','yyyymmdd') 
  5                                  and to_date('20060201','yyyymmdd')) a
  6        where rownum <= 20)
  7  where rowno >= 10;


     ROWNO         ID
---------- ----------
        10        123
        11        125
        12        129
        13        130
        14        131
        15        138
        16        142
        17        146
        18        147
        19        150
        20        151


11 rows selected.

格式2，

SQL> select * 
  2  from (select rownum as rowno, a.employee_id as id
  3        from (select employee_id from hr.employees 
  4              where hire_date between to_date('20050101','yyyymmdd') 
  5                                  and to_date('20060201','yyyymmdd')) a)
  6  where rowno between 10 and 20;


     ROWNO         ID
---------- ----------
        10        123
        11        125
        12        129
        13        130
        14        131
        15        138
        16        142
        17        146
        18        147
        19        150
        20        151


11 rows selected.

这两种格式，既有相同，又有不同，我们看下。

1. 相同点

(1) 这两种格式中内层的子查询，从语义上理解，应该读取所有符合条件的记录，即32条，

select employee_id from hr.employees 
where hire_date between to_date('20050101','yyyymmdd')
                    and to_date('20060201','yyyymmdd')

(2) 这两种格式返回的结果集是相同的，都是正确的。

2. 不同点

我们用执行计划，来看一下，

SQL> alter session set statistics_level=all;
Session altered.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

格式1实际只读取了20条记录，并不是32条，

格式2读取了32条记录，

这是为什么？

在CBO模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于格式1，第二层的查询条件where rowno >= 10就可以被推入到内层查询中，这样Oracle查询的结果一旦超过了rownum限制条件，就终止查询将结果返回了。从执行计划中，我们看到COUNT STOPKEY，点到为止，就是这个意思。

格式2，由于查询条件between 10 and 20，是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层,(即使推到最内层也没有意义，因为最内层查询不知道rowno代表什么)。因此，对格式2，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，从执行计划中，我们看到COUNT，没带STOPKEY，说明需要统计所有的数据。

如果数据量有限，这两种格式，相差无几，如同上面的测试，COST相同，但是当数据量很庞大的时候，因为格式1不需要读取所有数据，而格式2需要读取所有数据，然后再根据rownum筛选，显然格式1的效率要比格式2高。

我们再进一步，上面的测试，不知道大家看没看出一些问题？

两种格式中，子查询都是如下，没带任何排序，因此如果这张表的数据是实时更新的，很可能每次执行返回的结果集是不同的，如果这个分页的需求，对结果集的顺序是有要求的，这条SQL就是错的，而且可能很隐蔽，至于原因，《Oracle数据顺序问题》中说明了，Oracle中没有默认的数据读取顺序，唯一能让结果集有序的操作就是增加order by子句，

select employee_id from hr.employees 
where hire_date between to_date('20050101','yyyymmdd')
                    and to_date('20060201','yyyymmdd')

除此之外，可能还得注意，如果order by的字段，存在相同记录，查询结果集可能还是不确定的，需要order by有可以唯一确定记录的字段，例如可以用唯一索引字段、唯一约束字段或rowid，具体案例可参考《一个分页排序SQL查询结果集不确定的案例》。

一个分页操作，牵扯到的知识其实是很多的，如果不常用，确实容易忽视，就像我现在看之前经历过的案例，可能也会忘，还是得重新了解，一方面可能是知识点没吃透，另一方面还是需要注意日常的总结，形成适合自己的知识库和检索体系，在实践中学，从学回到实践，都是一种过程，只能慢慢体会了。

近期的热文：

《2020数据技术嘉年华》

《MySQL异常访问的熔断机制》

《Oracle时间戳类型内部表示的转换方式》

《Oracle的批量插入操作》

《数据库结构文档的生成利器》

《主键约束索引的奇葩现象》

《如何判断应用系统性能好不好？》

《Oracle Cloud创建19c数据库》

《SQL工具集-格式化结果的SQL》

《如何捕获问题SQL解决过度CPU消耗的问题》

《如何查看JVM运行的堆内存情况》

《Oracle删除字段的方式和风险，你都了解么？》

《登录缓慢的诡异问题》

《公众号600篇文章分类和索引》