开发者社区> 苍雪明南> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

分页查询的排序问题

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

前两天刚刚看完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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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信息无法推到查询内部,导致这种写法的执行效率很低。对比二种效率的例子,前面分页查询的文章中以及有很多了,这里就不在重复描述了。虽然这种方式也可以避免重复数据问题,但是不推荐使用这种方式。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
2021年软件类第十二届蓝桥杯第二场省赛 python组 F-J题解
2021年软件类第十二届蓝桥杯第二场省赛 python组 F-J题解
22 0
ES复杂查询-结果过滤, 排序,分页
ES复杂查询-结果过滤, 排序,分页
17 0
JavaWeb - 模糊查询 + 分页
JavaWeb - 模糊查询 + 分页
42 0
九、查询结果排序与分页
九、查询结果排序与分页
22 0
一个分页排序SQL查询结果集不确定的案例
前几天一位运价的兄弟提出一个关于分页排序SQL的问题,比较有意思,这里分享一下。 前些日子碰巧看了杨长老这篇文章:《让SQL成为一种生活方式:认识分页查询》,以下为原文摘要: Oracle的分页查询语句有两种基本格式。
968 0
分页查询优化
来源:一颗卤蛋 链接:http://www.cnblogs.com/lyroge/p/3837886.html 背景: 自己的一个网站,由于单表的数据记录高达了一百万条,造成数据访问很慢,Google分析的后台经常报告超时,尤其是页码大的页面更是慢的不行。
868 0
两个有序数组中查找第K大数
题目:两个数组A、B,长度分别为m、n,即A(m)、B(n),分别是递增数组。求第K大的数字。   方法一: 简单的办法,使用Merge Sort,首先将两个数组合并,然后在枚举查找。这个算法的时间复杂度是O(m+n)、空间复杂度也是O(M+n)。
1771 0
按照数组的形式排序+条件切换切换查询
//TOP50 public static function getTOP50 ($fromTime='',$endTime='',$type='warAllNum' ) { $fromTime = str_replace("-", "", substr($fromTime, 0...
487 0
+关注
苍雪明南
主流数据库:Oracle、MySQL、DB2 编程语言:Java、Python、shell 大数据工具:ZooKeeper、kafka
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载