办理Oracle分页查询中排序与效率问题-性能调优[Oracle防范]

原始未分页查询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<=20;  

这种办法能成功分页,后果以下:
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 <= 20   
)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 <= 20
)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 <= 20和RN > 10掌握分页查询的每页的范围.
第二种办法在大大都情形拥有较高的效率.分页的目的就是掌握输出后果集大小,在上面的分页查询语句中,这种考虑主要表目前WHERE ROWNUM <= 20这句上.

    挑选第11到20条记录存在两种办法,第二种办法恰是在查询的第二层通过ROWNUM <= 20来掌握最大值,在查询的最外层掌握最小值.而第一种办法是去掉查询第二层的WHERE ROWNUM <= 20语句,在查询的最外层掌握分页的最小值和最大值.

    普通来说,第二个查询的效率比第一个高得多,这是由于CBO 优化情势下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的履行效率.关于第二个查询语句,第2层的查询条件WHERE ROWNUM <= 20便可以被Oracle推入到内层查询中,这Oracle查询的后果一旦超越了ROWNUM限制条件,就终止查询将后果返回了.

    而第一个查询语句,由于查询条件Where rn>10 and rn<=20是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层（即便推到最内层也没有意义,因为最内层查询不知道RN代表什么）.因此,关于第一个查询语句,Oracle最内层返回给中间层的是全部满意条件的数据,而中间层返回给最外层的也是全部数据.数据的过滤在最外层完成,明显这个效率要比第二个查询低得多.

    这种分页关于单表查询、多表查询一样有效. 　　以上是“办理Oracle分页查询中排序与效率问题-性能调优[Oracle防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：