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 条评论
登录 后参与评论

相关文章

来自专栏码匠的流水账

JDK11的ZGC小试牛刀

ZGC全称是Z Garbage Collector,是一款可伸缩(scalable)的低延迟(low latency garbage)、并发(concurren...

1052
来自专栏影子

PostgreSQL=>递归查询

895
来自专栏Pulsar-V

OpenCV编译参数一览

全部参数一览 //Path to a program. ANT_EXECUTABLE:FILEPATH=D:/apache-ant-1.10.1/bin/ant...

4486
来自专栏10km的专栏

OpenBLAS:改进OpenBLASConfig.cmake生成方式,解除cmake脚本的路径依赖

问题描述 当我们用OpenBLAS提供的Makefile进行编译,并用make install PREFIX=/you/install/path,安装到指定的路...

3407
来自专栏乐沙弥的世界

Oracle Scheduler Chain 的用法

      Oracle CHAIN,也就是链,是将一串需要完成的作业连在一起,根据每一个步骤完成的不同结果来确定下面的哪一个动作需要被完成。实际上也就是if ...

631
来自专栏程序猿

SQL Server基础SQL脚本之创建架构、排序

代码420行左右 本系列,几乎都是代码,记得当时写的时候用的是微软的官方实例数据库AdventureWorks_Data.mdf、AdventureWork...

2758
来自专栏数据和云

案例分析:倾斜值传入导致 SQL 资源消耗升高

作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

1064
来自专栏数据和云

深入分析:12C ASM Normal冗余中PDB文件块号与AU关系与恢复

在 10G 和 11G 中,DBA 可以根据文件名,确定这个文件在 ASM 磁盘组上的分布,然后 dd 出来每一个 AU,最后拼凑成一个完成的数据文件。

1034
来自专栏贾老师の博客

CMake 使用

713
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(三) (r3笔记第69天)

关于查询转换,已经讨论了视图合并和子查询解嵌套,还有谓词推进和物化视图查询重写也是查询转换中不可或缺的部分。 -->谓词推进 这个术语听起来高大上,有点故弄玄虚...

26811

扫码关注云+社区