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

相关文章

来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------多表查询

  SUM(SAL)                                                                      ...

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

循序渐进调优union相关的sql(r2笔记23天)

今天在生产中发现一条sql语句消耗了大量的cpu资源。使用top -c来查看。 PID USER PR NI VIRT RES SHR S...

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

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

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

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

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

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

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

2759
来自专栏数据库新发现

OPTIMIZER_INDEX_COST_ADJ与成本计算

OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间. 该参数表示索引扫描和全表扫描成本的比较。缺省值1...

1072
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------,多行函数

  SUM(SAL)                                                                      ...

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

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2125
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

1373
来自专栏数据和云

深入解析:半连接与反连接的原理和等价改写方法

半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

3287

扫码关注云+社区