ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 使用rownum进行分页的陷阱

oracle 使用rownum进行分页的陷阱

原创 Linux操作系统 作者:aaqwsh 时间:2011-06-09 11:08:10 0 删除 编辑

同事遇到一个奇怪的问题,在使用下面分页导记录时发现分页后的记录跟总的记录数匹配(总的记录数大概25000),但是有些记录不存在,有些重复:

 

SELECT *

  FROM (SELECT row_.*, rownum rn

          FROM (select --省略几百行

                 order by sdate, mname, blevel asc) row_

         WHERE rownum <= 10000)

WHERE RN > 0

 

SELECT *

  FROM (SELECT row_.*, rownum rn

          FROM (select --省略几百行

                 order by sdate, mname, blevel asc) row_

         WHERE rownum <= 20000)

WHERE RN > 10000

 

SELECT *

  FROM (SELECT row_.*, rownum rn

          FROM (select --省略几百行

                 order by sdate, mname, blevel asc) row_

         WHERE rownum <= 30000)

WHERE RN > 20000

 

可能原因:

1  分页1--10000,10001--2000020000--30000sql执行计划不同,造成结果集记录的顺序不同(这个也许有可能,没有验证过)

2  排序算法(原来的3个字段(sdate, mname, blevel)不能保证唯一性)

 

 

能解释的现象:

1  总的记录数是一样的:因为总的记录数是不变的,不管记录的顺序怎么变,3段加起来的和是相等的

2  3段记录里有相同的记录,又有些记录不存在,分3段取时,记录集里记录的顺序是不确定的,可能第一次取到了,第二次因为记录顺序的变化,rownum变化了,又归到了那个区间

 

解决方法:

在原有的3个排序字段后,增加一个唯一性的排序字段(如no),当然,这是有代价的:

order by sdate, mname, blevel asc,no

 

关于rownum是怎么产生的(网上有不少的文章,下面是摘录):

rownum是在where条件过滤之后,在任何排序(order by)或聚集(aggregation)之前赋给行的。同时,只有当rownum被分配给行后才会递增。rownum的初始值为1rownum在查询中产生后就不再变化:

select * from emp where ROWNUM <= 5 order by sal desc;

该语句的目的是想返回top 5薪水最高的员工信息,但根据rownum的产生原理,rownumorder by之前就已经产生,所以该语句并不能起到top 5的作用,正确的语法如下:

select * from (select * from emp order by sal desc) where ROWNUM <= 5;

 

关于COUNT STOPKEY SORT ORDER BY STOPKEY需要了解其机制。

可以通过下面的查询类似模拟一下(增加hints是为了打乱结果集里记录顺序,类似模拟select  order by xx 多次运行记录的顺序不一样)

 

如下的语句如果按emp.job排序取前八条记录,7698 BLAKE7566 JONES7782 CLARK其中之一都有可能取到,都可能取不到(从直观想象,3条记录都是MANAGER,rownum都可能是7 8 9)

SQL> select  emp.*,dpt.dname from scott.emp emp ,scott.dept dpt

  2  where emp.deptno=dpt.deptno order by emp.job;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DNAME

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 RESEARCH

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 RESEARCH

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 ACCOUNTING

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 SALES

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 RESEARCH

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 RESEARCH

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 SALES

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 RESEARCH

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 ACCOUNTING

      7839 KING       PRESIDENT            17-NOV-81       5000                    10 ACCOUNTING

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 SALES

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DNAME

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 SALES

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 SALES

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 SALES

 

14 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-697459/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    262185