Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出
ORDERBY非稳定的排序
提一个问题：oracle在orderby排序时，是稳定排序算法吗？发现用一个type进行排序后，做分页查询，第一页的数据和第二页的数据有重复怀疑是orderby时，两次排列的顺序不一致
看到业务描述的问题可以得到的结论orderby排序不稳定，还有第一个印象就是，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，没有索引
SELECTROWNUM,ZZ_TEST.*FROMZZ_TEST;
12test 22test 33test 44test 51test
可以看到，默认差的时候是是按照rownum排序的。
然后按照name排序，
SELECTROWNUM,ZZ_TEST.*FROMZZ_TESTORDERBYZZ_TEST."name"
12test 22test 51test 44test 33test
可以看到，排列的顺序不是按照rownum来排序了。
这里再插入一个知识，如何在oracle里查看执行计划，我敲了半天的explain发现没有用。。。
原来是这么看的，而且消息要比mysql详细多了。：
select*fromtable(dbms_xplan.display());
------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------- |0|SELECTSTATEMENT||1|8|16(7)|00:00:01| |1|SORTORDERBY||1|8|16(7)|00:00:01| |2|COUNT|||||| |3|TABLEACCESSFULL|ZZ_TEST|1|8|15(0)|00:00:01| -------------------------------------------------------------------------------好了，那么排序和索引有没有关系呢？
我们先在type上面加一个索引试试，这里我清空了重新插入了5个数据
SELECTROWNUM,ZZ_TEST.*FROMZZ_TESTORDERBYZZ_TEST."name"
13test 24test 52test 41test 35test
貌似不给力啊老湿。
好，删掉type的索引，在id上加索引，清空表再插入5个数据
SELECTROWNUM,ZZ_TEST.*FROMZZ_TESTORDERBYZZ_TEST."name"
13test 24test 52test 41test 35test
好吧。原来带上索引都不给力啊。。。
但是不对啊。。。总感觉不对劲啊。没错。。。我TMD一直再用的rownum而不是rowID啊。我一定是最近写分页写多了，坑爹啊。
这里简单的分辨一下rownum和rowid的区别，rownum是返回结果集的一个伪数列，用来标记返回结果的顺序，而rowid是一个物理值用来标记存储位置的。这个值是唯一而固定的
rowid和rownum都是虚列，但含义完全不同。rowid是物理地址，用于定位oracle中具体数据的物理存储位置，而rownum则是sql的输出结果排序。通俗的讲：rowid是相对不变的，rownum会变化，尤其是使用orderby的时候。
那么我们再查下rowid试试，这时候表没有索引
SELECTrowidasrono,ROWNUM,ZZ_TEST.*FROMZZ_TESTORDERBYZZ_TEST."name"
AAA7JjAB9AAAD+RAAA13test AAA7JjAB9AAAD+RAAB24test AAA7JjAB9AAAD+RAAG52test AAA7JjAB9AAAD+RAAD41test AAA7JjAB9AAAD+RAAC35test
感觉rowno和rowid一个样子啊
清空表，再在name上建立一个索引，然后在插入5条数据
AAA7JjAB9AAAD+RAAA13test AAA7JjAB9AAAD+RAAB24test AAA7JjAB9AAAD+RAAG52test AAA7JjAB9AAAD+RAAD41test AAA7JjAB9AAAD+RAAC35test
所以，也不是rowid的问题，oralce的排序就是不稳定的。
这里有个小技巧，因为rownum的输出顺序并不是排序的结果那么如何能输出排序顺序的rownum呢？可以使用嵌套查询，这个和分页写法是一个道理的
selectROWNUM,t.*from(SELECTrowidrono,ZZ_TEST.*FROMZZ_TESTORDERBYZZ_TEST."name")t
这里再插入一个小知识，如何在oracle下看表的
select*fromuser_tables
可以查询出所有的用户表
selecttable_namefromuser_tables;
查询结果按照in条件顺序输出序输出
业务需要，通过lucene查出符合搜索条件的id，然后在详情表里查出这些id的详情
SELECTid,QUESTION,QUESTIONCOMMENTFROM"ASKDBA_QUESTION"whereIDIN(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顺序来排序的解决方案，是利用sqlserver的charindex来解决的。不过仅限于sqlserver
selectid,titlefrominfo
whereidin('3,1,2,5,4')
orderbycharindex(','+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来进行模糊匹配
selectname,passfromdps_userwhere
charindex('张三',dps_user.name)>0
但是oracle下是怎么实现相同的效果的呢？可以使用decode函数
SELECTid,QUESTION,QUESTIONCOMMENTFROM"ASKDBA_QUESTION"whereIDIN(63,62,65,61,64)ORDERBY"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
结果是符合条件的