分页查询的排序问题

最新推荐文章于 2022-06-14 17:15:32 发布
最新推荐文章于 2022-06-14 17:15:32 发布
阅读量459 收藏
点赞数
文章标签： 数据库 数据结构与算法

前两天刚刚看完Tom的EFFECTIVE ORACLE BY DESIGN的第八章，从里面学到不是东西。同时也发现一个问题：Tom给出的关于分页以及ROWNUM的时候，几乎包含了我前几篇关于分页文章中的所有关键点。

于是，打算补充一篇关于分页排序问题的文章，这篇文章中的内容是Tom书中没有的。


SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON EXP
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6236 BCB CCC
6235 AL_U1 CCC
6234 AL_P CCC
6240 BCF_U1 CCC
6239 BCF_P CCC
6238 BCF CCC
6237 BCB_U1 CCC

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

上面例子给出的就是分页查询的标准写法，对于查询前N条数据具有最高的效率。

但是这种分页排序语句存在一个问题：

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6245 BDF_P CCC
6243 BDF_I_BS_KEY CCC
6241 BCF_U2 CCC
6239 BCF_P CCC
6237 BCB_U1 CCC
6236 BCB CCC
6235 AL_U1 CCC

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

对比这次的结果和第一次的结果，就会发现ID为6235的数据出现了两次。第一次在前10条返回记录中，6235出现了，而第二次在11到第20条记录中，6235又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。

其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。

在这个例子中，OWNER列包含了大量键值为CCC的数据，而且Oracle的排序算法不具有稳定性，因此前10行记录和前20行记录中键值的顺序不能保证一致。因此，就造成某些数据会重复出现，而有些数据不会出现的现象。

解决这个问题其实也很简单。有两种方法可以考虑。

一，在使用不唯一的字段排序时，后面跟一个唯一的字段。

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6235 AL_U1 CCC
6236 BCB CCC
6237 BCB_U1 CCC
6238 BCF CCC
6239 BCF_P CCC
6240 BCF_U1 CCC

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6241 BCF_U2 CCC
6242 BDF CCC
6243 BDF_I_BS_KEY CCC
6244 BDF_I_DF_KEY CCC
6245 BDF_P CCC
6246 BDF_U1 CCC
6247 BP CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6249 BP_P CCC
6250 BP_U1 CCC

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。

这种方法最简单，且对性能的影响最小。另一种方法就是使用前面给出过多次的BETWEEN AND的方法。

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 1 AND 10;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6238 BCF CCC
6240 BCF_U1 CCC
6242 BDF CCC
6244 BDF_I_DF_KEY CCC
6246 BDF_U1 CCC
6255 BRL_U1 CCC

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 11 AND 20;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6254 BRL_P CCC
6253 BRL_I_DTS CCC
6252 BRL_I_BS_KEY CCC
6251 BRL CCC
6250 BP_U1 CCC
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6264 CCF CCC
6263 CCB_U1 CCC

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。

但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低。对比二种效率的例子，前面分页查询的文章中以及有很多了，这里就不在重复描述了。虽然这种方式也可以避免重复数据问题，但是不推荐使用这种方式。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14270146/viewspace-669370/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14270146/viewspace-669370/

cuanzhong6087
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
如何在Redis中实现分页排序查询过程解析
08-18
主要介绍了如何在Redis中实现分页排序查询过程解析,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
MySQL之排序查询与分页查询
黑马程序员官方博客
05-27 1436
一、 排序查询语法 排序查询语法： select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] 语法说明: 先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推 asc从小到大排列，即升序 desc从大到小排序，即降序 默认按照列值从小到大排序（即asc关键字） 例1：查询未删除男生信息，按学号降序: select * from students where gender=1 and is_delete=0 o
oracle分页查询的排序问题
行云流水
03-22 2289
 今天遇到一个很奇怪的oracle问题，就是在分页的同时进行排序操作，竟然出现重复的记录，在第2页还可能看到第1页已出现的记录。在网上搜了一下，发现这篇文章写得比较清楚:http://yangtingkun.itpub.net/post/468/112274在http://www.faqts.com/knowledge_base/view.phtml/aid/16331/fid/148这里也有说明
oracle分页查询并按照某字段进行排序的数种解决方案
旺仔专栏
09-23 3053
方案一（对于大数据集， 会明显的影响查询效率）：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 an
分页查询也能进行排序
ggjklncffd的博客
06-14 241
废话不多说代码简短，看了就能知道。
分页查询
01-19
结论：如果使用排序，而且还想使用rownum行号的话，需要使用嵌套查询 4.语法 --3.倒序获取前三条记录 ---结论：如果使用排序，而且还想使用rownum行号的话，需要使用嵌套查询 select rownum,t.* from (sele
如何解决Oracle分页查询中排序与效率问题
03-04
本文将结合作者近日工作中，在ORACLE数据库分页查询时，遇到一个小问题，为大家讲解如何解决Oracle分页查询中排序与效率问题。
jQuery动态表格数据分页检索排序代码
04-08
jQuery动态表格数据分页检索排序代码，纯前端实现搜索功能（非原创）
ASP.NET MVC分页和排序功能实现
01-02
分页和排序，应该是软件开发中，需要必知必会的技能了，对于分页，网上很多教程，当然，别人终究是别人的，只有自己理解，会了，并且吸收之后，再用自己的语言，传授出来，这才是硬道理。好了，废话说多了。现在我们...
oracle不排序分页结果随机,关于ORACLE排序后分页查询出现反复数据的探讨
weixin_39605414的博客
04-09 769
关于ORACLE排序后分页查询出现重复数据的探讨最近在帮一个同事调试BUG，有一个条BUG是这样描述的：所有有排序功能的列表，当其中某一字段当前页面内容一致时，翻页就有误。页码改变，列表内容不变。看到这个BUG第一反应是查看翻页的SQL语句。调试后找到SQL语句为：SELECT * FROM(SELECT BIZ.*,ROWNUM RN FROM(SELECT * FROM WIKI_BIZ B ...
分页查询多类数据且不同排序规则（解决方案）
UckyK的专栏
03-27 1065
开篇：最近做了一个需求分享一下思路，加急数据按照时间降序排列并且置顶，非加急的按照升序排列， 属于讲两类数据按照不同排序规则进行分页查询问题，具体需求如图所示； 刚接触需求的时候感觉也就那么回事吧！还行，不难！就是一个分页呗！ 进入详细设计的我眉头一皱！发现问题没这么简单，最开始考虑无非就是把数据按照查出来再重排序呗， 后来经过反复推敲不行，分页是个问题，在数据量小的时候我...
MyBatis实现分页+查询结果按照自定义顺序排序
Mattscl的博客
01-04 1386
代码如下所示 @RequestMapping("/queryNewUserByOrderPage") public JsonResult queryNewUserByOrderPage(){ //分页工具只对PageHelper下的第一个select语句生效，也就是不会查出所有，仅根据查询数量要求进行数据获取 PageHelper pageHelper = new PageHelper(); pageHelper.startPage(1, 3);
Oracle案例 分页查询和分组排序
qiudechao1的博客
10-16 666
表介绍 KM_REVIEW_MAIN：流程主表 字段DOC_SUBJECT，DOC_CREATE_TIME（流程创建时间），DOC_CREATOR_ID（即SUBMITTER），FD_DEPARTMENT_ID（提交人部门），DOC_STATUS， DOC_STATUS状态对应： --10草稿 --11驳回 --00废弃/提交人主动取消 --20待审 --30结束 I...
oracle分页排序查询,Oracle分页查询中排序与效率问题解决方法详解
weixin_35759767的博客
04-02 398
本文将结合作者近日工作中，在ORACLE数据库分页查询时，遇到一个小问题，为大家讲解如何解决Oracle分页查询中排序与效率问题。原始未分页查询Sql代码如下：select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC from tZDYSXt,tuser u,lborganization o where t.cjr=u.id and u.orgid...
对于Oracle中分页排序查询语句执行效率的比较分析
09-02 884
在工作中我们经常遇到需要在Oracle中进行分页、排序、查询的组合SQL语句，举例来说，通常我们会这样写：（假定表test中id是主键，并且id从1开始没有间断顺序排列）   1． SELECT * FROM (   SELECT id,a1,a2,a3,a4,a5,a6,
Oracle分页查询排序数据重复问题
weixin_30814223的博客
10-12 105
参考资料： 　　http://docs.oracle.com/database/122/SQLRF/ROWNUM-Pseudocolumn.htm#SQLRF00255 　　http://blog.csdn.net/u010395242/article/details/52047341 在采用以下所示的方式： SELECT * FROM (SELECT row_.*, rownu...
Oracle数据库中分页查询中排序及效率问题
wonderful的专栏
08-03 824
<br />原始未分页查询Sql代码如下：selectROWNUM rn,t. id ID, o.name YYB, u.name XM,t. MC from tZDYSX <br />t, tuser u, lborganization o wheret. cjr= u. id and u. orgid= o. orgcode andt. gx = 1 <br />orderby ID<br /> 结果如下：RNIDYYBXMMC349某证券总
条件分页排序处理
Chen4852010的博客
10-10 149
带条件的怎么处理 1.接收方式： 1.实体类，针对具体的条件封装实体类 2.requestParam() Map来接收，这样就通用点 2.对于排序字段的处理： 1.确定的排序字段，可以写在sql中，例如:String 具体的排序字段 只需要判断你是否排序字段为null,即可 2.对于不确定的排序字段，例如实体类中只有String sortColumn,如果用order by的话，sortColumn跟数据库的字段还存在一个 驼峰命名问题，导致不能直接排序 这时，我们先查询出分页数据，
mybatisplus分页查询排序
最新发布
08-01
MybatisPlus提供了自定义分页查询的功能，可以通过实现IPage接口来实现自定义分页查询。具体步骤如下： 1. 定义一个继承IPage接口的自定义分页类，例如： ``` public class MyPage<T> implements IPage<T> { ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值