Oracle數(shù)據(jù)庫(kù)中ORDER BY排序和查詢(xún)按IN條件的順序輸出

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

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

這里先科普下排序的穩(wěn)定性，舉個(gè)最簡(jiǎn)單的例子，1,2,3,1,4,5 排序 排序的結(jié)果是1,1,2,3,4,5，這時(shí)候觀察這個(gè)1，如果第一個(gè)1還是排序前的那個(gè)1，那么算法是穩(wěn)定的。也就是說(shuō)相等數(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ī)測(cè)試，插入幾條測(cè)試數(shù)據(jù)，表結(jié)構(gòu)就兩個(gè)字段，id和name，沒(méi)有索引

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST;

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

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

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

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

可以看到，排列的順序不是按照rownum來(lái)排序了。
這里再插入一個(gè)知識(shí)，如何在oracle里查看執(zhí)行計(jì)劃，我敲了半天的explain 發(fā)現(xiàn)沒(méi)有用。。。

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

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

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

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

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

貌似不給力啊老濕。
好，刪掉type的索引，在id上加索引，清空表再插入5個(gè)數(shù)據(jù)

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

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

好吧。原來(lái)帶上索引都不給力啊。。。

但是不對(duì)啊。。?？偢杏X(jué)不對(duì)勁啊。沒(méi)錯(cuò)。。。我TMD一直再用的rownum而不是rowID啊。我一定是最近寫(xiě)分頁(yè)寫(xiě)多了，坑爹啊。

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

rowid和rownum都是虛列，但含義完全不同。rowid是物理地址，用于定位oracle中具體數(shù)據(jù)的物理存儲(chǔ)位置，而rownum則是sql的輸出結(jié)果排序。通俗的講：rowid是相對(duì)不變的，rownum會(huì)變化，尤其是使用order by的時(shí)候。
那么我們?cè)俨橄聄owid試試，這時(shí)候表沒(méi)有索引

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

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

感覺(jué)rowno和rowid一個(gè)樣子啊

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

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

所以，也不是rowid的問(wèn)題，oralce的排序就是不穩(wěn)定的。
這里有個(gè)小技巧，因?yàn)閞ownum的輸出順序并不是排序的結(jié)果 那么如何能輸出排序順序的rownum呢？可以使用嵌套查詢(xún)，這個(gè)和分頁(yè)寫(xiě)法是一個(gè)道理的

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

這里再插入一個(gè)小知識(shí)，如何在oracle下看表的

select  *  from  user_tables

   可以查詢(xún)出所有的用戶(hù)表

select  table_name  from  user_tables;

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

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

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

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

61 測(cè)試問(wèn)題101 測(cè)試問(wèn)題101
62 測(cè)試問(wèn)題102 測(cè)試問(wèn)題102
63 測(cè)試問(wèn)題103 測(cè)試問(wèn)題103
64 測(cè)試問(wèn)題104 測(cè)試問(wèn)題104
65 測(cè)試問(wèn)題106 測(cè)試問(wèn)題106 

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

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

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

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

CHARINDEX ( expression1 , expression2 [ , start_location ] ) 

Expression1是要到expression2中尋找的字符中，start_location是CHARINDEX函數(shù)開(kāi)始在expression2中找expression1的位置。 CHARINDEX函數(shù)返回一個(gè)整數(shù)，返回的整數(shù)是要找的字符串在被找的字符串中的位置。假如CHARINDEX沒(méi)有找到要找的字符串，那么函數(shù)整數(shù)“0”
這里有小技巧，可以利用charindex來(lái)進(jìn)行模糊匹配

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

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

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);

63 測(cè)試問(wèn)題103 測(cè)試問(wèn)題103
62 測(cè)試問(wèn)題102 測(cè)試問(wèn)題102
65 測(cè)試問(wèn)題106 測(cè)試問(wèn)題106
61 測(cè)試問(wèn)題101 測(cè)試問(wèn)題101
64 測(cè)試問(wèn)題104 測(cè)試問(wèn)題104

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