Oracle排序陷阱
前几天遇到一个问题,发现Oracle存在一个排序问题,在这记录一下。
场景:
有一个类CMS的应用,数据库是Oracle的,其中有这样的一个功能:需要按照文章发布的日期排序分页显示。
程序是用Hibernate进行的QBC方式查询的(其实和查询方式无关,用HQL也一样。),20条一页,数据大概有130条左右,在应用里点下一页发 现只有前3页数据是变化的,之后4~6页显示的数据是一样的,也就是说4~6页没有做分页(或者分页数据不正确)。
经过一翻辛苦的查找,最后终于找到原因了,是因为后台管理人员执行了批量的文章发布,130多条记录里很多文章发布日期一摸一样。导致排序分页在4~6页 的时候数据老是重复。为什么会是这样?同样的数据我导入到MySQL下没有问题(存储引擎用的是InnoDB)。 问题出在Oracle,根据经验我觉得可能是Oracle的存储机制导致的,后来查找了Oralce相关的资料证实了这点。
一般我们用Oracle的表都是堆表(Oracle存储表的一种方式),对于堆表,必须知道他的机制:
全部扫描时,会按命中的顺序来获取数据,而不是以插入的顺序。这是一个必须了解的重要的数据库表概念:一般来讲,数据库表本质上是无序的数据集合。还应该 注意到,要观察到这种效果,不必在INSERT后接下来再使用DELETE;只需使用INSERT就可以得到同样的结果。如果我插入一个小行,那么观察到 的结果很可能是:取出行时默认的顺序为“小行、小行、大行”。这些行并不按插入的顺序获取。 Oracle会把数据放在能放下的任何地方,而不是按照日期或事务的某种顺序来存放。
如果你的查询需要按插入的顺序来获取数据,就必须向表中增加一列,以便获取数据时使用这个列对数据排序。例如,这可以是一个数字列,有一个递增的序列(使 用Oracle SEQUENCE对象)。只需使用一个SELECT,其ORDER BY子句对这个列完成排序,这样就可以模拟插入顺序。这个顺序可能只是近似的,因为序号为55的行很可能在序号为54的行之前提交,因此,数据库中序号为 55的行可能放在前面。
应该把堆组织表看作一个很大的无序行集合。这些行会以一种看来随机的顺序取出,而且取出的顺序还取决于所用的其他选项(并行查询、不同的优化器模式,等 待),同一个查询可能会以不同的顺序取出数据。不要过分依赖查询得到的顺序,除非查询中有一个ORDER BY语句!除非你的查询中有一个 ORDER BY,否则不要指望返回的数据会按某种顺序排序。 (另外,GROUP BY 也不会执行排序!ORDER BY 是无可替代的) 。
我出问题的应用虽然用了排序,但是由于排序基于的发布日期字段存在大量的重复数据,导致Oracle的排序出现问题,所以一般按照某些字段排序后,我们需 要再加一个order by id。
最后终结以下出现此问题的前提:
1、SQL分页
2、用了Oracle的堆表(如果你建表时候没有指定表类型,默认就是堆表)
3、按照且仅按照一个有大量重复数据的字段排序
满足1U2U3之后就会出现我遇到的问题。
分享到:
相关推荐
ORACLE 排序优化
对Oracle 排序中的几种常用排序的介绍。主要介绍一些常用的排序
oracle排序方法,按拼音,部首,笔画排序,有效实用!
浅谈Oracle优化排序的操作,ORACLE 参数调整影响
oracle中对排序的总结
Oracle对排序操作的优化措施 1、PGA与SGA的区别 2、为排序设置合理的排序区大小 3、会话区保存着用户的权限等重要信息
oracle索引陷阱,比较精深的oracle索引的用法,要注意的
本文将结合作者近日工作中,在ORACLE数据库分页查询时,遇到一个小问题,为大家讲解如何解决Oracle分页查询中排序与效率问题。
oracle__汉字排序
oracle如何实现分组排序和统计、聚集,如何分组求top N,什么是over分析函数,row_number(),rank(),dense_rank()区别又是什么, 如何找到一条记录的前后值,这份文档写得太好了。
解决Oracle分页查询中排序与效率问题解决Oracle分页查询中排序与效率问题解决Oracle分页查询中排序与效率问题解决Oracle分页查询中排序与效率问题
上面语句表示,根据col1分组,在分组内部根据col2排序,这里的“别名”的值就是每组内部排序后的序列号(组内连续的、唯一的),“[partition by col1] ”可以省略。
Oracle外排序研究的研究最新成果,致力于ORACLE和外排序算法研究的可以看看。
Oracle9i之前,中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。 1、设置NLS_SORT参数值 SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序 SCHINESE_STROKE_M ...
对于需要存储中文的oracle数据库,我们在查询时如果需要按照汉字拼音对结果集进行排序时应该怎么做?看完本文档你就知道了。
总结三种排序方式的共同点和不同点,已经排除空值后排序的oracle语句用法
主要介绍了Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出的方法,其中ORDER BY的排序结果需要注意其是否稳定,需要的朋友可以参考下
NULL 博文链接:https://cqh520llr.iteye.com/blog/1058352
数据库采用oracle,完成jsp增删改查、查询、过滤、分页、排序功能。对刚刚接触jsp的软件开发人员来说绝对是最好的范例。
Oracle外排序研究.pdf