Oracle数据库中ORDER BY排序和查询按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
好吧。原来带上索引都不给力啊。。。
但是不对啊。。。总感觉不对劲啊。没错。。。我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
结果是符合条件的
下一篇: 爬虫之selenium模块