Oracle——分页查询出现重复数据问题的分析与解决

问题描述

    在一次对Oracle数据库的查询操作中,根据以下条件对数据库进行查询读取,并分页显示(默认一页500条数据,共约三万条数据),结果不同的页码中出现了重复的数据。

//sql:sql语句
//params:sql中查询的参数
//pageParam:分页参数(每页数据数,以及当前页码)
//jTemp:排序字段
//dbType:数据库类型,mysql、oracle、impala
Pagination(String sql, Object[] params, PageParam pageParam, JdbcTem jTemp,String dbType)

    通过查询后台日志,获取到的sql语句描述如下:

--根据sort字段对dbname进行排序,每五百条数据一页
SELECT * FROM  (  
	SELECT A.*, ROWNUM RN  FROM ( 
		select * from dbname
		where condition 
		order by sort asc ) A  
	WHERE ROWNUM <=7000 )  
WHERE RN >6500

问题分析

可能的问题原因
数据库本身有重复数据。
分页数据有交集
排序字段不唯一

    在作分页查询时,其中使用的排序条件数据是根据用户ID(但在此表中不唯一,非主键) 排序。结果查询的结果,很多页的数据都有重复的。在排除其他问题后,考虑到应该是排序的字段值不唯一,造成每次查询时的数据排序不一定都是一样,每次查询的分页顺序结果不一致。
    在Oracle分页查询中,我们采用类似示例所示的公认的比较高效的数据库分页查询语句(Effective Oracle by Design中有描述、众多oracle使用者也做过测试)。这里的ROWNUM是一个伪列,它是oracle为查询结果所编的一个号,第一行的RN为1,第二行为2,以此类推。 因为oracle是按块进行读取数据的,如果数据按顺序存储,则可能使读取出来的数据是按顺序的,所以我们就会误解为默认排序。事实上,oracle没有进行任何排序操作,如果sql没有要求排序,oracle会顺序的从数据块中读取符合条件的数据返回到客户端。所以在没有使用排序sql的时候,分页返回的数据可能是按顺序的,也可能是杂乱无章的,这都取决与数据的存储位置。在分页查询过程中,如果数据的物理位置发生了改变,就可能会引起分页数据重复的现象。 所以,要正确使用分页查询,sql语句中必须有排序条件。但是,在有排序条件的时候,仍然会出现数据重复的现象,这是为什么呢?

Oracle数据库排序机理

    首先,sql的order by排序会对数据库产生很大的开销。然而,oracle的数据库实现就一个原则,怎么快怎么效率高就怎么来。在大多数情况下数据库操作是不需要排序的,所以Oracle在没有说明的情况下,会默认不排序。
    其次,这和oracle的表结构是有关系的,因为oracle的表结构默认是按堆存放的。按堆存放,通俗易懂的说,就是同一类(值相同的数据),存在相同的位置,但是这一堆数据是没有顺序的,存的时候就是乱序的;相对应的,取数据的时候也就是无序的。如果你建表的时候就是建的按索引组织的表,那么它返回的时候就会默认排序了。
    通过了解oracle的排序机理就会明白,出现这种情况的原因是没有进行排序或者因为排序列值的不唯一性。 Oracle这里使用的排序算法不具有稳定性,也就是说,对于键值相等的数据,这种算法完成排序后,不保证这些键值相等的数据保持排序前的顺序。

解决方法

    解决的方法是在后边增加一个唯一性列,比如主键。所以解决方法如下(两个条件必须同时满足):
1.sql语句中需要有排序条件。
2.排序条件如果没有唯一性,那么必须在后边跟上一个唯一性的条件,比如主键。如果没有主键,则可设置多个排序字段(但要保证多个排序字段确定一条数据)。
原问题解决方法可如下
有主键:

--根据sort字段对dbname进行排序,每五百条数据一页
SELECT * FROM  (  
  SELECT A.*, ROWNUM RN  FROM ( 
  	select * from dbname
  	where condition 
  	order by primaryKey asc ) A  
  WHERE ROWNUM <=7000 )  
WHERE RN >6500

无主键:

--根据sort字段对dbname进行排序,每五百条数据一页
SELECT * FROM  (  
   SELECT A.*, ROWNUM RN  FROM ( 
   	select * from dbname
   	where condition 
   	order by sort1,sort2,sort3 asc ) A  
   WHERE ROWNUM <=7000 )  
WHERE RN >6500
  • 3
    点赞
  • 5
    评论
  • 12
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值