這篇文章主要介紹了Oracle數(shù)據(jù)庫中ORDER BY排序和查詢按IN條件的順序輸出的方法,其中ORDER BY的排序結(jié)果需要注意其是否穩(wěn)定,需要的朋友可以參考下

ORDER BY非穩(wěn)定的排序

提一個問題： oracle在order by 排序時，是穩(wěn)定排序算法嗎? 發(fā)現(xiàn)用一個type進(jìn)行排序后，做分頁查詢，第一頁的數(shù)據(jù)和第二頁的數(shù)據(jù)有重復(fù) 懷疑是order by 時，兩次排列的順序不一致

看到業(yè)務(wù)描述的問題可以得到的結(jié)論order by排序不穩(wěn)定，還有第一個印象就是，type肯定是不唯一的，并且沒有索引吧。

這里先科普下排序的穩(wěn)定性，舉個最簡單的例子，1,2,3,1,4,5 排序 排序的結(jié)果是1,1,2,3,4,5，這時候觀察這個1，如果第一個1還是排序前的那個1，那么算法是穩(wěn)定的。也就是說相等數(shù)在排序后不發(fā)生交換。

還記得以前數(shù)據(jù)結(jié)構(gòu)中的幾種排序算法：

選擇排序復(fù)雜度為n*n，不穩(wěn)定排序，

快速排序復(fù)雜度為n*n，不穩(wěn)定排序，

希爾排序復(fù)雜度為nlogn，不穩(wěn)定排序，

堆排序復(fù)雜度為nlogn，不穩(wěn)定排序，

冒泡排序復(fù)雜度為n*n，穩(wěn)定排序，

插入排序復(fù)雜度為n*n，穩(wěn)定排序，

歸并排序復(fù)雜度為nlogn，穩(wěn)定排序

基數(shù)排序的復(fù)雜度和位數(shù)是有關(guān)的，是穩(wěn)定排序。

好了回到正題，本機(jī)測試，插入幾條測試數(shù)據(jù)，表結(jié)構(gòu)就兩個字段，id和name，沒有索引

1. SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST; 

1. 1 2 test 
2. 2 2 test 
3. 3 3 test 
4. 4 4 test 
5. 5 1 test 

可以看到，默認(rèn)差的時候是是按照rownum排序的。

然后按照name排序，

1. SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name" 

1. 1 2 test 
2. 2 2 test 
3. 5 1 test 
4. 4 4 test 
5. 3 3 test 

可以看到，排列的順序不是按照rownum來排序了。

這里再插入一個知識，如何在oracle里查看執(zhí)行計(jì)劃，我敲了半天的explain 發(fā)現(xiàn)沒有用。。。

原來是這么看的，而且消息要比mysql詳細(xì)多了。：

1. select * from table(dbms_xplan.display()); 

1. ------------------------------------------------------------------------------- 
2. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
3. ------------------------------------------------------------------------------- 
4. | 0 | SELECT STATEMENT | | 1 | 8 | 16 (7)| 00:00:01 | 
5. | 1 | SORT ORDER BY | | 1 | 8 | 16 (7)| 00:00:01 | 
6. | 2 | COUNT | | | | | | 
7. | 3 | TABLE ACCESS FULL| ZZ_TEST | 1 | 8 | 15 (0)| 00:00:01 | 
8. ------------------------------------------------------------------------------- 

好了，那么排序和索引有沒有關(guān)系呢?

我們先在type上面加一個索引試試，這里我清空了重新插入了5個數(shù)據(jù)

1. SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name" 

1. 1 3 test 
2. 2 4 test 
3. 5 2 test 
4. 4 1 test 
5. 3 5 test 

貌似不給力啊老濕。

好，刪掉type的索引，在id上加索引，清空表再插入5個數(shù)據(jù)

1. SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name" 

1. 1 3 test 
2. 2 4 test 
3. 5 2 test 
4. 4 1 test 
5. 3 5 test 

好吧。原來帶上索引都不給力啊。。。

但是不對啊。。。總感覺不對勁啊。沒錯。。。我TMD一直再用的rownum而不是rowID啊。我一定是最近寫分頁寫多了，坑爹啊。

這里簡單的分辨一下rownum和rowid的區(qū)別，rownum是返回結(jié)果集的一個偽數(shù)列，用來標(biāo)記返回結(jié)果的順序，而rowid是一個物理值用來標(biāo)記存儲位置的。這個值是唯一而固定的

rowid和rownum都是虛列，但含義完全不同。rowid是物理地址，用于定位oracle中具體數(shù)據(jù)的物理存儲位置，而rownum則是sql的輸出結(jié)果排序。通俗的講：rowid是相對不變的，rownum會變化，尤其是使用order by的時候。

那么我們再查下rowid試試，這時候表沒有索引

1. SELECT rowid as rono,ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name" 

1. AAA7JjAB9AAAD+RAAA 1 3 test 
2. AAA7JjAB9AAAD+RAAB 2 4 test 
3. AAA7JjAB9AAAD+RAAG 5 2 test 
4. AAA7JjAB9AAAD+RAAD 4 1 test 
5. AAA7JjAB9AAAD+RAAC 3 5 test 

感覺rowno和rowid一個樣子啊

清空表，再在name上建立一個索引，然后在插入5條數(shù)據(jù)

1. AAA7JjAB9AAAD+RAAA 1 3 test 
2. AAA7JjAB9AAAD+RAAB 2 4 test 
3. AAA7JjAB9AAAD+RAAG 5 2 test 
4. AAA7JjAB9AAAD+RAAD 4 1 test 
5. AAA7JjAB9AAAD+RAAC 3 5 test 

所以，也不是rowid的問題，oralce的排序就是不穩(wěn)定的。

這里有個小技巧，因?yàn)閞ownum的輸出順序并不是排序的結(jié)果 那么如何能輸出排序順序的rownum呢?可以使用嵌套查詢，這個和分頁寫法是一個道理的

1. select ROWNUM ,t.* from (SELECT rowid rono,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name") t 

這里再插入一個小知識，如何在oracle下看表的

1. select * from user_tables 

可以查詢出所有的用戶表

1. select table_name from user_tables; 

查詢結(jié)果按照in條件順序輸出序輸出

業(yè)務(wù)需要，通過lucene查出符合搜索條件的id，然后在詳情表里查出這些id的詳情

1. SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64); 

其中id是根據(jù)搜索的權(quán)值進(jìn)行的排序，sql沒有問題，但是通過這種sql查出來的結(jié)果的排序就不對了。

1. 61 測試問題101 測試問題101 
2. 62 測試問題102 測試問題102 
3. 63 測試問題103 測試問題103 
4. 64 測試問題104 測試問題104 
5. 65 測試問題106 測試問題106  

這個一般默認(rèn)是按照主鍵來排序的，而并不是根據(jù)in中條件的順序來排列的

網(wǎng)上有個案例是按照in順序來排序的解決方案，是利用sql server的charindex來解決的。不過僅限于sqlserver

1. select id,title from info  
2. where id in ('3,1,2,5,4')  
3. order by charindex(','+convert(varchar,ID)+',',',3,1,2,5,4,')  

CHARINDEX函數(shù)返回字符或者字符串在另一個字符串中的起始位置。CHARINDEX函數(shù)調(diào)用方法如下：

1. CHARINDEX ( expression1 , expression2 [ , start_location ] ) 

Expression1是要到expression2中尋找的字符中，start_location是CHARINDEX函數(shù)開始在expression2中找expression1的位置。 CHARINDEX函數(shù)返回一個整數(shù)，返回的整數(shù)是要找的字符串在被找的字符串中的位置。假如CHARINDEX沒有找到要找的字符串，那么函數(shù)整數(shù)“0”

這里有小技巧，可以利用charindex來進(jìn)行模糊匹配

1. select name,pass from dps_user where 
2. charindex('張三',dps_user.name)> 0 

但是oracle下是怎么實(shí)現(xiàn)相同的效果的呢?可以使用decode函數(shù)

1. SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64) ORDER BY "DECODE"(id, 63,1,62,2,65,3,61,64); 

結(jié)果是符合條件的