Oracle数据库中ORDERBY排序和查询按IN条件的顺序输出
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
oracle数据库怎么查询表好吧。原来带上索引都不给⼒啊。。。
但是不对啊。。。总感觉不对劲啊。没错。。。我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);
63 测试问题103 测试问题103
62 测试问题102 测试问题102
65 测试问题106 测试问题106
61 测试问题101 测试问题101
64 测试问题104 测试问题104
结果是符合条件的

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。