挖出一些分页查询的秘密
今天看下分页查询这个知识点，能挖出什么样的知识。
测试表中符合条件的记录共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查询结果集不确定的案例》。
一个分页操作，牵扯到的知识其实是很多的，如果不常用，确实容易忽视，就像我现在看之前经历过的案例，可能也会忘，还是得重新了解，一方面可能是知识点没吃透，另一方面还是需要注意日常的总结，形成适合自己的知识库和检索体系，在实践中学，从学回到实践，都是一种过程，只能慢慢体会了。