oracle 高效分页
oracle高效分页
什么是分页查询
对于基于Web的应用而言，对查询的结果集进行分页是一个比较常见的需求。假设浏览器界面每页可以显示10条记录，最初界面显示头10条记录给用 户，当终端用户点击“下一页”按钮时，界面显示接下来的10条记录。一般来说，Web后台服务程序并不是一次性的把所有符合条件的记录都返回给浏览器，再 由浏览器应用程序对查询结果进行分页。现在的普遍做法都是：当用户要浏览下一页时，浏览器重新从WEB后台服务器取出下10条记录。
对于采用了数据库的WEB应用来说，如何对查询的结果进行分页就有两种实现方式，一种是WEB后台程序把全部查询结果取到内存中，由它实现分页。另一种是每次只从数据库取出10条记录，由数据库实现分页。
这两种分页方式各有优缺点，有时可能把这两种方式结合起来应用。在这里，我主要介绍一下如何在Oracle数据库中实现分页查询。
如何实现分页查询
认识ROWNUM
Oracle的ROWNUM伪列是实现结果集分页的关键，可能有很多人对于ROWNUM伪列到底代表什么还不太清楚，有人甚至认为它是数据库表中记 录的编号。下面我引用在ASKTOM网站上的他一个例子帮助大家认识一下ROWNUM到底为何物。为了帮助大家理解，我建了一个测试表，然后再插入20条 测试数据，当前原例子中查询语句表名和字段也做了相应的修改。
--建测试表
create table t_testrownum
( rid number, rvalue varchar2(30))
--插入测试数据
begin
insert into t_testrownum values(1, 'aaaa');
insert into t_testrownum values(2, 'aaaa');
insert into t_testrownum values(3, 'aaaa1');
insert into t_testrownum values(4, 'aaaa');
insert into t_testrownum values(5, 'aaaa');
insert into t_testrownum values(6, 'aaaa');
insert into t_testrownum values(7, 'aaaa');
insert into t_testrownum values(8, 'aaaa4');
insert into t_testrownum values(9, 'aaaa');
insert into t_testrownum values(10, 'aaaa');
insert into t_testrownum values(11, 'aaaa');
insert into t_testrownum values(12, 'aaaa');
insert into t_testrownum values(13, 'aaaa5');
insert into t_testrownum values(14, 'aaaa');
insert into t_testrownum values(15, 'aaaa');
insert into t_testrownum values(16, 'aaaa');
insert into t_testrownum values(17, 'aaaa');
insert into t_testrownum values(18, 'aaaa8');
insert into t_testrownum values(19, 'aaaa');
insert into t_testrownum values(20, 'aaaa');
end;
例1．
select * from t_testrownum where rownum = 1; 返回结果集的第一条记录。那么select * from T where rownum = 2;应该返回结果集的第二条记录。可是实际上第二个查询语句不会返回任何记录，为什么呢？
类似的例子还有：
select * from T where rownum >= 1 and rownum <= 5; 返回前5条记录，而
select * from T where rownum >= 2 and rownum <= 5; 无记录返回。
其实：ROWNUM并是记录编号，而是Oracle在向外输出结果集中的记录时给它赋的一个顺序号。当不在查询语句中限制ROWNUM时，其处理逻辑如下所示：
rownum = 1
for x in ( select * from T )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;
当限制ROWNUM时，我们对比一下下面两个查询的执行计划：
语句1：select * from t_testrownum;
语句1的执行计划：
SELECT STATEMENT Optimizer Mode=CHOOSE TABLE
ACCESS FULL USDPD502.T_TESTROWNUM
语句2：select * from t_testrownum where rownum <= 10;
语句2的执行计划
SELECT STATEMENT Optimizer Mode=CHOOSE
COUNT STOPKEY
TABLE ACCESS FULL USDPD502.T_TESTROWNUM
通过对比，我们可以看出语句2的执行计划中增加了一条‘COUNT STOPKEY’，该句的意思是当rownum已超出指定范围时，停止输出，其处理逻辑如下：
rownum = 1
for x in ( select * from T )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
if ( rownum已超出指定范围 )
then
跳出循环
end if;
end loop;
至此，我们就可以解释上面两个例子中的问题了。当我们限制rownum=1时，第一条记录满足该条件，输出该记录，rownum增1，由于 rownum已超出范围，停止输出。当我们限制rownum=2时，由于第一条记录不满足条件，不输出该记录，rownum也不增加。接着取第二条记录， 由于rownum此时还是1，不满足条件，同样也不输出，如此直到遍历全部记录结束循环。
基本的分页查询
当知道rownum是怎么回事后，我们就可以利用它来实现分页查询了。假如我们想从表T中取出第11条到第20条记录，在未透彻了解ROWNUM之前，许多人可能会写出下面的查询语句。
--语句1
select * t_testrownum a where rownum >= 11 and rownum <= 20;
通过前面的分析，我们知道，这样的写法是错误的。所以，我们把它修改为如下的写法。
--语句2
select *
from ( select a.*, rownum r from t_testrownum a )
where r>= 11 and r <= 20
该语句的输出结果是正确的，它的内层查询先从表t_testrownum中查询出所有记录，同时为每条记录赋一个顺序编号r，外层查询再限制只选取编号为11到20之间的记录。
从查询效率上考虑一下，如果我们只需要得到第11到20条之间的记录，那么在内层查询中就可以利用rownum限制内层查询输出的记录数。修改后的语句如下：
--语句3
select *
from ( select a.*, rownum r
from t_testrownum a where rownum <= 20)
where r>= 11
需要排序的分页查询
有人会想，排序那还不简单吗，加上order by 子句就行了。
--语句4
select *
from ( select a.*, rownum r from t_testrownum a where rownum <= 20 order by rvalue)
where r>= 11
我们都知道order by是对输出的结果集进行排序，而不是先排序然后输出结果集。语句4的实际效果是，从表t_testrownum中取出前20行记录，然后按照rvalue字段排序，输出排序编号大于等于11的记录。
只对前20条记录进行排序显然不是我们所期望的，为避免这个问题，有人可能会把上面的语句做如下修改：
--语句5
select *
from ( select a.*, rownum r from t_testrownum a order by rvalue ）
where r>= 11 and r <= 20
同样，由于rownum 在排序之前就确定了，我们得到得记录并不是排序后的第11到20条记录，而是排序前的第11到20条记录。为得到我们期望的结果，我们必须把rownum r放到order by的外面。修改后的查询语句如下。
--语句6
select *
from ( select b.*, rownum r
from ( select a.*
from t_testrownum a order by rvalue ) b
where rownum <= 20 )
where r>= 11
如果排序字段rvalue的值在表t_testrownum中是唯一的，那么上面的语句从功能实现上来说，就没什么问题了。但是如果rvalue字 段的值不唯一，假设按rvalue排序后，前1到20条记录的rvalue字段的值是相同的，我们先查出第1到10条记录，然后再查出第11到20条记 录，这是我们会发现，同一条记录可能同时出现在这两个查询结果集中。这是为什么呢。一开始，我认为是Oracle采用的排序算法是不稳定的，两个相同的值 在两次排序中的顺序是不固定的。但是我们把语句select a.* from t_testrownum a order by rvalue执行10次，却发现输出结果集的排序顺序都是一致的。那么是什么导致排序不一致呢。为此，我们观察了一下语句6的执行计划：
SELECT STATEMENT, GOAL = CHOOSE
VIEW Object owner=USDPD502
COUNT STOPKEY
VIEW Object owner=USDPD502
SORT ORDER BY STOPKEY
TABLE ACCESS FULL Objectowner=USDPD502 Objectname=T_TESTROWNUM
从执行计划中我们看出，执行计划的第2步是“SORT ORDER BY STOPKEY”，它表示
其并不是对所有符合条件的记录完全排序，而是仅仅找到符合排序条件的指定条数的记录，比如我们限制rownum <= 20，则只需找到排序在前20位的记录。记得在Oracle的官方文档上我曾经见到Oracle声称其排序是稳定的一致的。前面我们将select a.* from t_testrownum a order by rvalue执行10次，发现排序是一致的。那 么“SORT ORDER BY STOPKEY”方式的排序是否是一致的呢？我们将语句6执行10次同样发现，其结果是一致的。那么为什么我们用语句6查第1到10条记录和11到20条 记录时，有些记录为什么在这两个查询中出现的名次并不一致呢。
--语句7
select *
from ( select a.* from t_testrownum a order by rvalue )
where rownum <= 20
--语句8
select *
from ( select a.* from t_testrownum a order by rvalue )
where rownum <= 10
为了找出排序不一致的原因，我们分别执行语句7和语句8，这时你会发现，前10名的记录在两次查询中并不一样。为此，我们得出结论，当 stopkey不同时，排序结果是不同的。为什么会这样呢，大师TOM的解释是“SORT ORDER BY STOPKEY”是Oracle为优化TOPN（查询排序后的前N条记录）查询采用的一种算法。大致的思想是：先取出为排序时前面的N条记录，对这N条记 录排序，然后用后面的剩下的所有记录依排序要求插入前N条记录中。一般来说，这样的插入排序也应该是稳定的，那为什么N不同，排序结果就不一样呢？下面的 两条查询语句似乎可以给你一点启发：
--语句9
select a.*, rownum r
from t_testrownum a
whererownum <= 10
orderby rvalue
--语句10
select a.*, rownum r
from t_testrownum a
whererownum <= 20
orderby rvalue
它们的执行计划如下：
SELECT STATEMENT, GOAL = CHOOSE
SORT ORDER BY
COUNT STOPKEY
TABLE ACCESS FULL Object owner=USDPD502 Object name=T_TESTROWNUM
从上面两条查询语句的结果我们可以看出，排在前面的10记录也不是一致的。要注意的是，这两条语句的执行计划中并没有使用“SORT ORDER BY STOPKEY”算法。而是普通的排序“SORT ORDER BY”。只是这两次排序的记录条数不一样，这时有些人可能会怀疑是在排序前两次查询输出记录的顺序就是不一样的。我们可以这么测试一下，先对 t_testrownum表的所有20条记录排序，然后从表中删除掉后10条记录，从语句9和语句10中删除掉whererownum <= N条件，我们发现，查询结果和语句9和语句10是一样的。由此，我们可以得出结论，当参与排序的记录数量不同时，具有相同值的记录的排序顺序是不同的。
进行分页查询时，如果同一条记录在多个分页中出现，这样的结果肯定不是你所期望的。为了避免这种现象的发生，一个简单的方法就是在排序条件中增加辅助排序字段，使得每条记录的组合排序字段是唯一的。
如何在分页查询中避免排序
对于需要排序的分页查询来说，如果参与排序的结果集很大，而实际返回的记录数很少，那么有两点是需要注意的：第一大结果集排序对系统资源的占用，第 二如果排序字段的值不唯一，某些记录会出现在多个分页中。如何避免以上的两点呢，我们知道，索引的键值是有序组织的，我们是否可以利用索引来避免排序呢。 答案是肯定的，我们在rvalue上建立索引：
--语句11
create index idx_testrownum_rvalue on t_testrownum(rvalue);
这时，我们把语句6修改一下：
--语句12
select *
from ( select b.*, rownum r
from ( select a.*
from t_testrownum a
where rvalue > chr(1) order by rvalue ) b
where rownum <= 20 )
where r>= 11
执行语句12，它的执行计划如下：
SELECT STATEMENT, GOAL = CHOOSE
VIEW Object owner=USDPD502
COUNT STOPKEY
VIEW Object owner=USDPD502
TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM
INDEX RANGE SCAN Object owner=USDPD502 Object name=IDX_TESTROWNUM_RVALUE
从上面的执行计划中，我们已看不到“SORT ORDER BY STOPKEY”字样，说明没有排序步骤。那么记录在分页中重复的问题是否也解决了呢，经过测试，该问题也不复存在。
那么如果我们需要降序排序呢? 对于降序排序，我们需要增加相应的hints来提示优化器走降序索引扫描。
-语句13
select *
from ( select b.*, rownum r
from ( select/*+index_desc(a idx_testrownum_rvalue)*/a.*
from t_testrownum a
where rvalue > chr(1) order by rvalue desc ) b
where rownum <= 20 )
where r>= 11
语句13的执行计划如下：
SELECT STATEMENT, GOAL = CHOOSE
VIEW Object owner=USDPD502
COUNT STOPKEY
VIEW Object owner=USDPD502
TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM
INDEXRANGE SCAN DESCENDING Objectowner=USDPD502
利用索引避免排序需要的注意点
虽然使用索引来避免排序是一个好方法，但是，任何事物都不可能是十全十美的，使用该方法时需要注意以下几点：
1) 排序字段上的索引必须是升序索引，如果使用降序索引将导致升序排序时分页出现问题。（具体是什么原因我现在还没弄明白，如果有知道原因的可以指点一下）
2) 在分页开始记录数大于10000后，利用索引排序进行分页的性能反而不如直接排序分页的方式好。（如果字段是数字性，性能下降不大，如果是字符串则性能下降明显，这可能和字符串和数字的比较方式不同有关）