SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值和最小值。详情请阅读:让SQL成为一种生活方式:认识分页查询

今天来继续讨论分页查询的排序问题。

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=10Bytes=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=20Bytes=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=10Bytes=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=20Bytes=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=6363Bytes=693567) 2 1 COUNT 3 2 VIEW(Cost=72 Card=6363 Bytes=610848) 4 3 SORT (ORDER BY) (Cost=72Card=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=6363Bytes=693567) 2 1 COUNT 3 2 VIEW(Cost=72 Card=6363 Bytes=610848) 4 3 SORT (ORDER BY) (Cost=72Card=6363 Bytes=165438) 5 4 TABLE ACCESS(FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

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

但是正是由于使用了全排序,而且ROWNUM信息无法推到查询内部,导致这种写法的执行效率很低。虽然这种方式也可以避免重复数据问题,但是不推荐使用这种方式。

关于分页查询的更多知识,请继续关注后期的分享。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-06-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Pulsar-V

Save Camera Document

#pragma once #include "HCCamera.h" #include <time.h> #include <cstdio> #incl...

2828
来自专栏linux驱动个人学习

高通Audio中ASOC的machine驱动

ASoC被分为Machine、Platform和Codec三大部分,其中的Machine驱动负责Platform和Codec之间的耦合以及部分和设备或板子特定的...

9774
来自专栏高性能服务器开发

(八)高性能服务器架构设计总结3——以flamigo服务器代码为例

再看filezilla,一款ftp工具的服务器端,它采用的是Windows的WSAAsyncSelect模型(代码下载地址:https://github.com...

1191
来自专栏码匠的流水账

聊聊spring cloud的HystrixAutoConfiguration

本文主要研究一下spring cloud的HystrixAutoConfiguration

982
来自专栏MelonTeam专栏

Bitmap 源码阅读笔记

导语: Android 系统上的图片的处理,跟Bitmap 这个类脱不了关系,我们有必要去深入阅读里面的源码,以便在工作中能更好的处理Bitmap相关的问题...

2498
来自专栏一个会写诗的程序员的博客

【Mac IDEA Java Web项目Tomcat启动报错】java.net.MalformedURLException: Local host name unknown

java.net.MalformedURLException: Local host name unknown

773
来自专栏c#开发者

自定义ios keyboard tool bar代码片段

-(UIToolbar *)createActionBar { UIToolbar *actionBar = [[UIToolbar alloc] in...

2536
来自专栏搞前端的李蚊子

Html5模拟通讯录人员排序(sen.js)

// JavaScript Document  var PY_Json_Str = ""; var PY_Str_1 = ""; var PY_Str_...

5906
来自专栏Hadoop数据仓库

Oracle sqlldr 如何导入一个日期列

1. LOAD DATA INFILE * INTO TABLE test FIELDS TERMINATED BY X'9' TRAILING NULLCO...

1796
来自专栏我和未来有约会

简练的视图模型 ViewModel

patterns & practices Developer Center 发布了 Unity Application Block 1.2 for Silver...

2179

扫码关注云+社区