【转】解决Oracle分页查询中排序与效率问题-优快云博客

本文介绍了在Oracle数据库中实现高效分页查询的方法。通过对比两种不同的分页查询SQL语句，展示如何通过调整ROWNUM的位置来优化查询效率，并解释了Oracle在CBO优化模式下对外层查询条件的处理方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转自http://www.yqdown.com/chengxukaifa/Oracle/895.htm

原始未分页查询Sql代码如下：

select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC from tZDYSX 
t,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 
order by ID

结果如下：

RN ID YYB XM MC

---------- ----------------- ----------------------

3 49 某证券总部管理员测试

4 96 某证券总部管理员持有上港10000股以上

5 102 某证券总部管理员十年规划

14 105 某证券总部管理员开发渠道为上海

11 106 某证券总部管理员万科A

12 107 某证券总部管理员 11

13 108 某证券总部管理员今天过生日的客户

2 109 某证券总部管理员客户状态正常

6 110 某证券总部管理员无交易

7 111 某证券总部管理员 OA

8 112 某证券总部管理员幸运客户

9 113 某证券总部管理员风险型

10 114 某证券总部管理员 tst

22 115 白沙网上交易安昌彪安客户正常

1 118 某证券总部管理员 213

18 119 某证券总部管理员客户号包含1008

17 120 某证券总部管理员 aaa

19 123 某证券总部管理员 ssssssss

20 124 某证券总部管理员 www

21 126 某证券总部管理员 123123

15 127 某证券总部管理员 1212

16 128 某证券总部管理员 aaaaaa

22 rows selected

最初我使用如下Sql代码查询：

 select * from (select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC 
from tZDYSX t,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and 
t.gx = 1 order by t.ID )Where rn>10 and rn

这种方法能成功分页，结果如下：

RN ID YYB XM MC

---------- ----------------- -------------------

14 105 某证券总部管理员开发渠道为上海

11 106 某证券总部管理员万科A

12 107 某证券总部管理员 11

13 108 某证券总部管理员今天过生日的客户

18 119 某证券总部管理员客户号包含1008

17 120 某证券总部管理员 aaa

19 123 某证券总部管理员 ssssssss

20 124 某证券总部管理员 www

15 127 某证券总部管理员 1212

16 128 某证券总部管理员 aaaaaa

10 rows selected

从结果看来，有个问题：此语句Sql代码

order by CJSJ DESC 被执行，但是是在分后的第11到20条记录的结果集中再进行排序，而不是先排序后分页。(本来希望显示ID为112到126，结果变为105到128)

后来变为以下Sql代码查询：

SELECT * 
FROM( 
SELECT ROWNUM RN,TA.* 
FROM( 
select t.id ID, o.name YYB,u.name XM, t.MC 
from tZDYSX t,tuser u,lborganization o 
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID 
)TA WHERE ROWNUM )WHERE RN > 10 
SELECT * 
FROM( 
SELECT ROWNUM RN,TA.* 
FROM(
select t.id ID, o.name YYB,u.name XM, t.MC
from tZDYSX t,tuser u,lborganization o 
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID
)TA WHERE ROWNUM )WHERE RN > 10

结果如下：

RN ID YYB XM MC

---------- ----------------- -------------------

11 112 某证券总部管理员幸运客户

12 113 某证券总部管理员风险型

13 114 某证券总部管理员 tst

14 115 白沙网上交易安昌彪安客户正常

15 118 某证券总部管理员 213

16 119 某证券总部管理员客户号包含1008

17 120 某证券总部管理员 aaa

18 123 某证券总部管理员 ssssssss

19 124 某证券总部管理员 www

20 126 某证券总部管理员 123123

10 rows selected

看来结果是正确的。

总结：第二种方法其中最内层的查询Sql代码

select t.id ID, o.name YYB,u.name XM, t.MC 
from tZDYSX t,tuser u,lborganization o 
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID

表示不进行翻页的原始查询语句。ROWNUM 10控制分页查询的每页的范围。

第二种方法在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM

选择第11到20条记录存在两种方法，第二种方法正是在查询的第二层通过ROWNUM

一般来说，第二个查询的效率比第一个高得多，这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第二个查询语句，第2层的查询条件WHERE ROWNUM

而第一个查询语句，由于查询条件Where rn>10 and rn

这种分页对于单表查询、多表查询一样有效。