提一个问题: 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;
12test
22test
33test
44test
51test 可以看到,默认差的时候是是按照rownum排序的。
然后按照name排序,
SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
12test
22test
51test
44test
33test 可以看到,排列的顺序不是按照rownum来排序了。
这里再插入一个知识,如何在oracle里查看执行计划,我敲了半天的explain 发现没有用。。。
原来是这么看的,而且消息要比mysql详细多了。:
Explain plan for SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
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"
13test
24test
52test
41test
35test 貌似不给力啊老湿。
好,删掉type的索引,在id上加索引,清空表再插入5个数据
SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
13test
24test
52test
41test
35test
好吧。原来带上索引都不给力啊。。。
但是不对啊。。。总感觉不对劲啊。没错。。。我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+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呢?可以使用嵌套查询,这个和分页写法是一个道理的
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; 查看其它信息可以参考这个博客:
http://jerryhui.blog.51cto.com/109787/241127
这里有一个博客的实验结果是很符合预期的,但是我是真的没有做出这个结果:
http://blog.csdn.net/gushangzao/article/details/7251138
http://www.itpub.net/forum.php?mod=viewthread&tid=1339419
/**
20121102 oracle的表类型
*/
http://www.2cto.com/database/201110/109559.html
原来oracle也分这么多表类型,感觉和mysql的不同的引擎差不多的。看这个介绍好像oracle的数据存储确实和表类型有关,但是我找了半天不资料还是不知道怎么查看和更改表类型。求些靠谱资料。。。
总结一下,oralce的排序是不稳定的排序,对于相同字段的排序输出结果貌似和索引和rowid都没有规律可循,和网上的某些结论不太一致,这也是我今天比较困惑的地方。另外熟悉几种排序的稳定性,会查看oracle的执行计划,oracle如何看表的名及表结构,知道rownum和rowid的区别,知道通过嵌套查询输出有规律的rownum