ORDER BY非穩(wěn)定的排序
提一個問題: oracle在order by 排序時,是穩(wěn)定排序算法嗎? 發(fā)現(xiàn)用一個type進行排序后,做分頁查詢,第一頁的數(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)定排序。
好了回到正題,本機測試,插入幾條測試數(shù)據(jù),表結(jié)構(gòu)就兩個字段,id和name,沒有索引
SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST;
1 2 test
2 2 test
3 3 test
4 4 test
5 1 test
可以看到,默認(rèn)差的時候是是按照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里查看執(zhí)行計劃,我敲了半天的explain 發(fā)現(xiàn)沒有用。。。
原來是這么看的,而且消息要比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 |
-------------------------------------------------------------------------------
好了,那么排序和索引有沒有關(guān)系呢?
我們先在type上面加一個索引試試,這里我清空了重新插入了5個數(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
貌似不給力啊老濕。
好,刪掉type的索引,在id上加索引,清空表再插入5個數(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
好吧。原來帶上索引都不給力啊。。。
但是不對啊。。??偢杏X不對勁啊。沒錯。。。我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試試,這時候表沒有索引
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條數(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的問題,oralce的排序就是不穩(wěn)定的。
這里有個小技巧,因為rownum的輸出順序并不是排序的結(jié)果 那么如何能輸出排序順序的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;
查詢結(jié)果按照in條件順序輸出序輸出
業(yè)務(wù)需要,通過lucene查出符合搜索條件的id,然后在詳情表里查出這些id的詳情
SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64);
其中id是根據(jù)搜索的權(quán)值進行的排序,sql沒有問題,但是通過這種sql查出來的結(jié)果的排序就不對了。
61 測試問題101 測試問題101
62 測試問題102 測試問題102
63 測試問題103 測試問題103
64 測試問題104 測試問題104
65 測試問題106 測試問題106
這個一般默認(rèn)是按照主鍵來排序的,而并不是根據(jù)in中條件的順序來排列的
網(wǎng)上有個案例是按照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函數(shù)返回字符或者字符串在另一個字符串中的起始位置。CHARINDEX函數(shù)調(diào)用方法如下:
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中尋找的字符中,start_location是CHARINDEX函數(shù)開始在expression2中找expression1的位置。 CHARINDEX函數(shù)返回一個整數(shù),返回的整數(shù)是要找的字符串在被找的字符串中的位置。假如CHARINDEX沒有找到要找的字符串,那么函數(shù)整數(shù)“0”
這里有小技巧,可以利用charindex來進行模糊匹配
select name,pass from dps_user where
charindex('張三',dps_user.name)> 0
但是oracle下是怎么實現(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 測試問題103 測試問題103
62 測試問題102 測試問題102
65 測試問題106 測試問題106
61 測試問題101 測試問題101
64 測試問題104 測試問題104
結(jié)果是符合條件的
您可能感興趣的文章:- Oracle SQL樹形結(jié)構(gòu)查詢
- Oracle數(shù)據(jù)庫中的級聯(lián)查詢、級聯(lián)刪除、級聯(lián)更新操作教程
- Oracle、MySQL和SqlServe三種數(shù)據(jù)庫分頁查詢語句的區(qū)別介紹
- 講解Oracle數(shù)據(jù)庫中的數(shù)據(jù)字典及相關(guān)SQL查詢用法
- 一些Oracle數(shù)據(jù)庫中的查詢優(yōu)化建議綜合
- ORACLE錯誤一覽表 方便查詢
- ORACLE 查詢被鎖住的對象,并結(jié)束其會話的方法
- oracle跨庫查詢dblink的用法實例詳解
- Oracle中scott表結(jié)構(gòu)與簡單查詢實例分析
- mysql、mssql及oracle分頁查詢方法詳解
- Oracle 數(shù)據(jù)庫特殊查詢總結(jié)