转自:http://www.linuxidc.com/Linux/2011-02/32593p2.htm
在oracle分页查询中,我们采用类似以下所示的公认的比较高效的数据库分页查询语句(Effective Oracle by Design中有描述、众多oracle使用者也做过测试)。
写道
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= n)
WHERE RN > m
基于Rownum进行分页,表面上看运行起来似乎也没问题。但经过我们实践检验,其实实际上有时是不安全的,在某些情况下会出错,原因在于它没有排序。在分页情况下,第一页和第二页的数据是来自两次相对独立的SQL,如果没有排序,则SQL第一次和第二次执行时返回的结果是不一致的。
不一致是什么意思?假设有一个无排序的SQL,我们把SQL执行两次:
第一次执行后会返回有1、2、3、4、5共5条记录
第二次执行后还是会返回有1、2、3、4、5共5条记录
大部分情况下,这两次返回结果的顺序是完全一样的。但不幸的是,也许数据库有问题了,也许有人改了数据,反正有时候它会不一样,比如第二次执行时第2条和第4条对调了,返回的是1、4、3、2、5共5条记录,如下:
第一次:1、2、3、4、5
第二次:1、4、3、2、5
假设我们对这个SQL进行分页,每页3条记录,共两页,正常情况下结果是这样的:
拉第一页时,执行第一次SQL,按1、2、3、4、5排序,返回1、2、3三条记录
拉第二页时,执行第一次SQL,按1、2、3、4、5排序,返回4、5两条记录
但如果发生排序混乱的问题,结果会这样:
拉第一页时,执行第一次SQL,按1、2、3、4、5排序,返回1、2、3三条记录
拉第二页时,执行第二次SQL,按1、4、3、2、5排序,返回2、5两条记录
结果我们会发现,分页结果很不正常,2这条记录出现了两次,4则消失了。正常来说,我们不会注意到有数据丢失,但我们会注意到分页的数据有重复。
怎么办呢?那我们就加一个排序吧,排序子句要加在最里层的SQL里,这样分页出来的结果才会是排序后的结果。比如按名称、类别或作者排序的order by子句:
select xxx.*
from (
select rownum as recordno
from (
select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME_TYPE'
order by NAME,ATYPE,CREATOR
) xx
) xxx
where recordno >= :开始记录号
and recordno <= :结束记录号
这样是不是可以了呢?答案还是不行,因为这些字段的值不是唯一的。可考虑一个极端情况,就是这个表里500万条记录的名称、类别和作者都完全一样,会有什么结果呢?结果仍然是无序。
最终解决这个问题的办法,就是一定要用ID主键排序。不管前面有多少个order by字段,最后面一定要加上ID主键:
select xxx.*
from (
select rownum as recordno
from (
select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME_TYPE'
order by NAME,ATYPE,CREATOR,ID
) xx
) xxx
where recordno >= :开始记录号
and recordno <= :结束记录号
由于主键ID是唯一的,所以只要ID不变,按ID排序就能保证每次执行分页SQL都是一致的顺序了。
分享到:
相关推荐
C# SQL2005 分页排序存储过程 C# SQL2005 分页排序存储过程
mybatis中,sqlserver分页
不错的经典的分页、排序SQL 通用存储过程,可以大大节省Web数据库中大批量记录展现的性能问题。
分页就是按照某种规则显示分组数据集,但是在SQL Server 中,分页并不是十分容易就能够实现。随着SQL Server的发布,其中的一些排序函数使得开发人员编写数据分页程序变得更加简单和高效。这些新的排序函数提供了...
本文将结合作者近日工作中,在ORACLE数据库分页查询时,遇到一个小问题,为大家讲解如何解决Oracle分页查询中排序与效率问题。
按照参数能够支持各种列的排序,返回数据第一个为表,第二个为数据总数.
sql server分页代码 --分页【top】:不支持复合主键 SELECT TOP 10 * from Lend where id not in (SELECT TOP ((2-1)*10) id from lend ORDER BY id) ORDER BY id ; SELECT TOP 10 * from Lend where id not ...
sql分页存储过程 CREATE PROCEDURE [dbo].[dbTab_PagerHelper] @TableName VARCHAR(50), --表名 @FieldNames VARCHAR(1000), --显示列名,如果是全部字段则为* @WhereString VARCHAR(256) = NULL, --查询条件 ...
简易的sqlserver分页功能,只需要将页码传入即可...3、传入mybitis中 在sql语句头部调用 ${pading.begin} 结尾调用 ${pading.end}即可实现分页 4、默认每页10条 若需要更改 则修改该类源码中 的成员变量即可(有注释)
通用sql分页存储过程,提供12参数可供选择。其中,提供两种分页方案被选择和相关排序方式,支持自定义查询、自定义排序等
sqlserver2005的select语句top子句可以跟参数,这样就很好的解决了分页的问题,通用的写法就是: select 【排序列】,【显示列1...N】 from TableName where 【排序列】 in select top (@PageNo*@PageSize) ...
sql学习 合并重复行 定义新的列为其添加数据 新组成的表安某字段排序 只复制表结构 分页语句
SQL 分页存贮过程(切换表头字段名<点表头排序>,切换正反排序)——非主键分页。
单从数据角度看, 我们多次读取之间的间隔时间足够让数据发生一些变化, 在一次只展示一屏时, 我们很难发现这些问题(因此不影响用户体验), 然而当一页展示100屏数据时, 这种变化会被放大, 此时, 数据重复/跳跃的问题就...
使用游标实现的sql2000可用分页存储过程,不要增加排序字段
这是一个高兴能sql分页语句,不需要在sqlserver里写存储过程了。只需要简单地将表,所需字段,条件,排序输入即可。
jquery无刷新分页、排序 利用jquery实现的ajax无刷新分页以及排序,支持多种格式无刷新更换,数据库采用的是Sql2000自带的NorthWind数据库 数据库控制使用的是SqlHelper
第05章_排序与分页.sql
帮助你一步上步的学习linq to sql 中分页功能的实现。且带例子进行讲解,让你更容易理解、学习linq to sql 知识。
C#拼接SQL语句,SQL Server 2005+,多行多列大数据量情况下,使用ROW_NUMBER实现的高效分页排序