关于order by中的数据排序(r4笔记第6天)

今天开发的一个同事找到我,说碰到一个比较奇怪的问题,说两个等价的查询类似下面的形式。 select *from test where account_id=xxxxxx order by creation_date select *from test where account_id=xxxxxx and entity_id=xxxxx order by creation_date 两个查询都会返回4条结果,但是第一个查询和第二个查询的结果排序结果不一致。 使用第一个查询的结果如下: ------------------ ---------- ---------- ------------------- ---------- 09-AUG-14 170000501 2 130000000003403 09-AUG-14 170000501 1 130000000003403 09-SEP-14 170000501 3 130000000003403 11-SEP-14 170000501 4 130000000003403 使用第2个查询的结果如下 ------------------ ---------- ---------- ------------------- ---------- 09-AUG-14 170000501 1 130000000003403 09-AUG-14 170000501 2 130000000003403 09-SEP-14 170000501 3 130000000003403 11-SEP-14 170000501 4 130000000003403 order by的时候是根据第1个字段排序的,但是第3个字段的排序结果却不同。单纯从sql语句的角度来说,似乎也是合乎情理的。 当时首先想到的就是把creation_date格式化为更加精细的日期格式,精确到秒,看看时间,结果查看了最终的日期格式,发现精度都一样,印象中10g以后的order by算法做了变更。是不是这个原因导致的呢。 为了证明我的想法,自己做了一个简要的测试,反复的比较之后发现order by在指定字段排序后,其它字段的排序和以下的几种场景有关。 和rowid有一定的关系 和索引相关 和null值相关 为了证明,我在反复尝试之后,使用了下面的例子。 我们创建一个表test,然后插入一些针对性的数据。 create table test(creation_date date,ACCOUNT_ID number,INST_FROM number,TLG_INST_ID number, CHG_SEQ_NO number); insert into test values(to_date('2014-09-11','yyyy-mm-dd'),170000501,4,130000000003403,''); insert into test values(to_date('2014-08-09','yyyy-mm-dd'),170000501,2,130000000003403,''); insert into test values(to_date('2014-08-09','yyyy-mm-dd'),170000501,1,130000000003403,16310); insert into test values(to_date('2014-09-0','yyyy-mm-dd'),170000501,3,130000000003403,''); 这个时候查询结果,可以看到inst_from字段是按照4,2,1,3的顺序显示的。这个时候没有任何的排序操作。 SQL> select creation_date,ACCOUNT_ID,INST_FROM,TLG_INST_ID, CHG_SEQ_NO from test t where tlg_inst_id='130000000003403' CREATION_DATE ACCOUNT_ID INST_FROM TLG_INST_ID CHG_SEQ_NO ------------------ ---------- ---------- ------------------- ---------- 11-SEP-14 170000501 4 130000000003403 09-AUG-14 170000501 2 130000000003403 09-AUG-14 170000501 1 130000000003403 16310 09-SEP-14 170000501 3 130000000003403 我们略做改动,使用order by creation_date,可以看到inst_from字段会按照2,1,3,4的顺序显示了。这个时候做了排序操作,但是相对前2条数据,因为插入inst_from的顺序是按照先2,1的顺序来的,所以排序后的结果就是先2,1的顺序。 SQL> select creation_date,ACCOUNT_ID,INST_FROM,TLG_INST_ID, CHG_SEQ_NO from test t where tlg_inst_id='130000000003403' order by creation_date; CREATION_DATE ACCOUNT_ID INST_FROM TLG_INST_ID CHG_SEQ_NO ------------------ ---------- ---------- ------------------- ---------- 09-AUG-14 170000501 2 130000000003403 09-AUG-14 170000501 1 130000000003403 16310 09-SEP-14 170000501 3 130000000003403 11-SEP-14 170000501 4 130000000003403 这个时候我们创建一个索引,注意我们使用了一个含有空值的列 chg_seq_no. create index inx_test on test(TLG_INST_ID,CHG_SEQ_NO);

这个时候再次使用排序,结果集就有了明显的差别。 SQL> select creation_date,ACCOUNT_ID,INST_FROM,TLG_INST_ID, CHG_SEQ_NO from test t where tlg_inst_id='130000000003403' order by creation_date; CREATION_DATE ACCOUNT_ID INST_FROM TLG_INST_ID CHG_SEQ_NO ------------------ ---------- ---------- ------------------- ---------- 09-AUG-14 170000501 1 130000000003403 16310 09-AUG-14 170000501 2 130000000003403 09-SEP-14 170000501 3 130000000003403 11-SEP-14 170000501 4 130000000003403

值得注意的是,如果我们创建的索引不含有空值列,

create index inx_test on test(TLG_INST_ID);

输出的排序结果和没有创建索引没有什么区别。

SQL> select creation_date,ACCOUNT_ID,INST_FROM,TLG_INST_ID, CHG_SEQ_NO from test t where tlg_inst_id='130000000003403' order by creation_date;

CREATION_DATE ACCOUNT_ID INST_FROM TLG_INST_ID CHG_SEQ_NO

------------------ ---------- ---------- ------------------- ----------

09-AUG-14 170000501 2 130000000003403

09-AUG-14 170000501 1 130000000003403 16310

09-SEP-14 170000501 3 130000000003403

11-SEP-14 170000501 4 130000000003403

通过上面的测试,也发现在order by的时候还是存在很多的不确定性,这些都可以通过在order by之后指定排序的列来避免。但是对理解order by来说,这些测试还是能够看到order by在实现方式上还是有很多的技巧的。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-01-05

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库

干货!超过500行的Mysql学习笔记

本文为作者初学Mysql时做的笔记,囊括了Mysql相关基本知识,内容较多超过500行笔记,希望对大家有帮助。 ? /* 启动MySQL */ net star...

23560
来自专栏技术博文

Yii数据库操作方法指南

CDbConnection: 一个抽象数据库连接 CDbCommand: SQL statement CDbDataReader: 匹配结果集的一行记录 CDb...

30170
来自专栏乐沙弥的世界

使用 Toad 实现 SQL 优化

       It is very easy for us to implement sql tuning by toad.  We need to do is...

10220
来自专栏数据小魔方

MySQL数据库基础——本地文件交互

从这一篇开始,大概会花四五篇的内容篇幅,归纳整理一下之前学过的SQL数据库,一来可以为接下来数据分析工作提前巩固基础,二来把以前学的SQL内容系统化、结构化。 ...

364120
来自专栏性能与架构

MySQL 清除表空间碎片

表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白 当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用...

39970
来自专栏谈补锅

复杂sql分组查询 ( pivot)

      一个数据表里面字段有年、月、日、金额、支付方式等字段,然后现在想写个sql语句,把每一天的每种支付方式金额(支付方式有多重)排在同一行,

1.6K30
来自专栏MasiMaro 的技术博文

ADO对SQL Server 2008数据库的基础操作

最近在学习ADO与数据库的相关知识,现在我将自己学到的东西整理写出来,也算是对学习的一种复习。

16220
来自专栏JavaQ

三分钟学习分布式ID方案

在分布式系统中,当数据库数据量达到一定量级的时候,需要进行数据拆分、分库分表操作,传统使用方式的数据库自有的自增特性产生的主键ID已不能满足拆分的需求,它只能保...

14220
来自专栏涂小刚的专栏

Spark SQL 之 Join 实现

如今Spark SQL(Dataset/DataFrame)已经成为Spark应用程序开发的主流,作为开发者,我们有必要了解Join在Spark中是如何组织运行...

4K20
来自专栏ChaMd5安全团队

SQLI-LABS 更新帖(一)

docker搭建环境 请自己安装好docker,然后使用ubuntu:14.04的镜像 docker pull ubuntu:14.04 以下是pcat提供的...

39580

扫码关注云+社区

领取腾讯云代金券