解决Oracle分页查询中排序与效率问题.doc

解决Oracle分页查询中排序与效率问题原始未分页查询Sql代码如下： selectROWNUMrn,t.idID,YYB,XM,t.MCfromtZDYSXt,tuseru,lborganizationowheret.cjr=id=codeandt.gx=1orderbyID结果如下： RN ID YYBXM 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(selectROWNUMrn,t.idID,YYB,XM,t.MCfromtZDYSXt,tuseru,lborganizationowheret.cjr=id=codeandt.gx=1orderbyt.ID)Wherern10andrn=20;这种方法能成功分页，结果如下： RN ID YYBXM 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(SELECTROWNUMRN,TA.*FROM(selectt.idID,YYB,XM,t.MCfromtZDYSXt,tuseru,lborganizationowheret.cjr=id=codeandt.gx=1orderbyt.ID)TAWHEREROWNUM10SELECT*FROM(SELECTROWNUMRN,TA.*FROM(selectt.idID,YYB,XM,t.MCfromtZDYSXt,tuseru,lborganizationowheret.cjr=id=codeandt.gx=1orderbyt.ID)TAWHEREROWNUM10结果如下： RN ID YYBXM 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代码selectt.idID,YYB,XM,t.MCfromtZDYSXt,tuseru,lborganizationowheret.cjr=id=codeandt.gx=1orderbyt.ID表示不进行翻页的原始查询语句。ROWNUM 10控制分页查询的每页的范围。 第二种方法在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM = 20这句上。选择第11到20条记录存在两种方法，第二种方法正是在查询的第二层通过ROWNUM = 20来控制最大值，在查询的最外层控制最小值。而第一种方法是去掉查询第二层的WHERE ROWNUM = 20语句，在查询的最外层控制分页的最小值和最大值。一般来说，第二个查询的效率比第一个高得多，这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第二个查询语句，第2层的查询条件WHERE ROWNUM 10 and rn=20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也