這篇文章主要介紹了Oracle數據庫中ORDER BY排序和查詢按IN條件的順序輸出的方法,其中ORDER BY的排序結果需要注意其是否穩定,需要的朋友可以參考下
ORDER BY非穩定的排序
提一個問題： oracle在order by 排序時，是穩定排序算法嗎? 發現用一個type進行排序后，做分頁查詢，第一頁的數據和第二頁的數據有重復 懷疑是order by 時，兩次排列的順序不一致
看到業務描述的問題可以得到的結論order by排序不穩定，還有第一個印象就是，type肯定是不唯一的，并且沒有索引吧。
這里先科普下排序的穩定性，舉個最簡單的例子，1,2,3,1,4,5 排序 排序的結果是1,1,2,3,4,5，這時候觀察這個1，如果第一個1還是排序前的那個1，那么算法是穩定的。也就是說相等數在排序后不發生交換。
還記得以前數據結構中的幾種排序算法：
選擇排序復雜度為n*n，不穩定排序，
快速排序復雜度為n*n，不穩定排序，
希爾排序復雜度為nlogn，不穩定排序，
堆排序復雜度為nlogn，不穩定排序，
冒泡排序復雜度為n*n，穩定排序，
插入排序復雜度為n*n，穩定排序，
歸并排序復雜度為nlogn，穩定排序
基數排序的復雜度和位數是有關的，是穩定排序。
好了回到正題，本機測試，插入幾條測試數據，表結構就兩個字段，id和name，沒有索引
- SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST;
- 1 2 test
- 2 2 test
- 3 3 test
- 4 4 test
- 5 1 test
可以看到，默認差的時候是是按照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來排序了。
這里再插入一個知識，如何在oracle里查看執行計劃，我敲了半天的explain 發現沒有用。。。
原來是這么看的，而且消息要比mysql詳細多了。：
- 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 |
- -------------------------------------------------------------------------------
好了，那么排序和索引有沒有關系呢?
我們先在type上面加一個索引試試，這里我清空了重新插入了5個數據
- 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個數據
- 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
好吧。原來帶上索引都不給力啊。。。
但是不對啊。。。總感覺不對勁啊。沒錯。。。我TMD一直再用的rownum而不是rowID啊。我一定是最近寫分頁寫多了，坑爹啊。
這里簡單的分辨一下rownum和rowid的區別，rownum是返回結果集的一個偽數列，用來標記返回結果的順序，而rowid是一個物理值用來標記存儲位置的。這個值是唯一而固定的
rowid和rownum都是虛列，但含義完全不同。rowid是物理地址，用于定位oracle中具體數據的物理存儲位置，而rownum則是sql的輸出結果排序。通俗的講：rowid是相對不變的，rownum會變化，尤其是使用order by的時候。
那么我們再查下rowid試試，這時候表沒有索引
- 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
感覺rowno和rowid一個樣子啊
清空表，再在name上建立一個索引，然后在插入5條數據
- 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的問題，oralce的排序就是不穩定的。
這里有個小技巧，因為rownum的輸出順序并不是排序的結果 那么如何能輸出排序順序的rownum呢?可以使用嵌套查詢，這個和分頁寫法是一個道理的
- select ROWNUM ,t.* from (SELECT rowid rono,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name") t
這里再插入一個小知識，如何在oracle下看表的
- select * from user_tables
可以查詢出所有的用戶表
- select table_name from user_tables;
查詢結果按照in條件順序輸出序輸出
業務需要，通過lucene查出符合搜索條件的id，然后在詳情表里查出這些id的詳情
- SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64);
其中id是根據搜索的權值進行的排序，sql沒有問題，但是通過這種sql查出來的結果的排序就不對了。
- 61 測試問題101 測試問題101
- 62 測試問題102 測試問題102
- 63 測試問題103 測試問題103
- 64 測試問題104 測試問題104
- 65 測試問題106 測試問題106
這個一般默認是按照主鍵來排序的，而并不是根據in中條件的順序來排列的
網上有個案例是按照in順序來排序的解決方案，是利用sql server的charindex來解決的。不過僅限于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函數返回字符或者字符串在另一個字符串中的起始位置。CHARINDEX函數調用方法如下：
- CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中尋找的字符中，start_location是CHARINDEX函數開始在expression2中找expression1的位置。 CHARINDEX函數返回一個整數，返回的整數是要找的字符串在被找的字符串中的位置。假如CHARINDEX沒有找到要找的字符串，那么函數整數“0”
這里有小技巧，可以利用charindex來進行模糊匹配
- select name,pass from dps_user where
- charindex('張三',dps_user.name)> 0
但是oracle下是怎么實現相同的效果的呢?可以使用decode函數
- 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);
|
|
結果是符合條件的
新聞熱點
疑難解答
