今天開發的一個同事找到我，說碰到一個比較奇怪的問題，有兩個等價的查詢類似下面的形式。
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在實現方式上還是有很多的技巧的。

關於order by中的資料排序

相關文章