关于order by中的数据排序

最新推荐文章于 2023-11-15 11:18:06 发布

转载最新推荐文章于 2023-11-15 11:18:06 发布 · 744 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://blog.itpub.net/23718752/viewspace-1391408/

本文探讨了在Oracle数据库中使用ORDER BY子句时遇到的非预期排序行为现象，并通过实验揭示了这一现象背后的原理，包括ROWID、索引及NULL值的影响。

这个问题在分页查询时会导致分页数据丢失。

今天开发的一个同事找到我，说碰到一个比较奇怪的问题，有两个等价的查询类似下面的形式。
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在实现方式上还是有很多的技巧的。