关于order by中的数据排序

今天开发的一个同事找到我，说碰到一个比较奇怪的问题，有两个等价的查询类似下面的形式。
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);
值得注意的是，如果我们创建的索引不含有空值列，
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在实现方式上还是有很多的技巧的。