杰晶网络

Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出
数据库 2024/11/23 佚名
3 2

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

结果是符合条件的

标签：
Oracle,数据库,排序
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
www.imxmx.com 杰晶网络
8,675无损音乐
1,324高清电影
213破解软件
120,141站长资源
一句话新闻
苹果官宣WWDC 2024！预计会有大批AI功能 - 2024/11/23

3月27日消息，苹果宣布2024年全球开发者大会（WWDC）将于6月10日至6月14日举行，巧合的是，这次大会与端午假期重合。

苹果官方表示：

在线参加 Apple 每年规模最大的开发者盛会。亲眼见证 Apple 最新平台、技术和工具的发布。了解如何创建和改进你的 App 和游戏。与 Apple 设计师和工程师互动交流，与全球开发者社区建立联系。以上活动均免费在线举行。

探索各种新的工具、框架和功能，助力你打造出理想的 App 和游戏。通过视频讲座学习新技能，与 Apple 专家进行一对一会面，以推进你的项目，完善你的构思。

Swift Student Challenge 旨在支持和鼓舞下一代开发者、创作者和企业家。太平洋时间 3 月 28 日，我们将公布今年的获奖者名单。获奖者将有资格参加在 Apple Park 举办的特别活动。我们还会选出 50 名杰出获胜者，他们将受邀前往库比提诺，获得为期三天的非凡体验，包括参加 Apple Park 的特别活动。

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。

据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新动态

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网