oracle 使用rownum进行分页的陷阱

前端之家收集整理的这篇文章主要介绍了oracle 使用rownum进行分页的陷阱前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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

@H_301_4@

SELECT@H_301_4@*@H_301_4@

@H_301_4@FROM@H_301_4@(@H_301_4@SELECT@H_301_4@row_.*,@H_301_4@rownum@H_301_4@rn@H_301_4@

@H_301_4@FROM@H_301_4@(@H_301_4@select@H_301_4@--@H_301_4@省略几百行@H_301_4@

@H_301_4@order@H_301_4@@H_301_4@by@H_301_4@sdate,mname,blevel@H_301_4@asc@H_301_4@) row_@H_301_4@

@H_301_4@WHERE@H_301_4@@H_301_4@rownum@H_301_4@<=@H_301_4@10000@H_301_4@)@H_301_4@

WHERE@H_301_4@RN >@H_301_4@0@H_301_4@

@H_301_4@

@H_301_4@WHERE@H_301_4@@H_301_4@rownum@H_301_4@<=@H_301_4@20000@H_301_4@)@H_301_4@

WHERE@H_301_4@RN > 1000@H_301_4@0@H_301_4@

@H_301_4@WHERE@H_301_4@@H_301_4@rownum@H_301_4@<=@H_301_4@30000@H_301_4@)@H_301_4@

WHERE@H_301_4@RN > 2000@H_301_4@0@H_301_4@

@H_301_4@

可能原因:@H_301_4@

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

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

能解释的现象:@H_301_4@

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

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

解决方法@H_301_4@

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

order by sdate,blevel asc,no@H_301_4@

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

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

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

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

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

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

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

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

sql> selectemp.*,dpt.dname from scott.emp emp,scott.dept dpt

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

EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO DNAME

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

7788 SCOTTANALYST7566 19-APR-87300020 RESEARCH

7902 FORDANALYST7566 03-DEC-81300020 RESEARCH

7934 MILLERCLERK7782 23-JAN-82130010 ACCOUNTING

7900 JAMESCLERK7698 03-DEC-8195030 SALES

7369 SMITHCLERK7902 17-DEC-8080020 RESEARCH

7876 ADAMSCLERK7788 23-MAY-87110020 RESEARCH

7698 BLAKEMANAGER7839 01-MAY-81285030 SALES

7566 JONESMANAGER7839 02-APR-81297520 RESEARCH

7782 CLARKMANAGER7839 09-JUN-81245010 ACCOUNTING

7839 KINGPRESIDENT17-NOV-81500010 ACCOUNTING

7844 TURNERSALESMAN7698 08-SEP-811500030 SALES

7654 MARTINSALESMAN7698 28-SEP-811250140030 SALES

7521 WARDSALESMAN7698 22-FEB-81125050030 SALES

7499 ALLENSALESMAN7698 20-FEB-81160030030 SALES

14 rows selected.


本来想一起发的,老是报错,不让提交,内容太长?。

四个测试语句(增加hints是为了打乱结果集里记录顺序,类似模拟select order by xx 多次运行记录的顺序不一样)、执行计划、结果:
select * from (
select tmp.*,rownum rn from (
select /*+ use_nl(emp dpt) */ emp.*,scott.dept dpt
where emp.deptno=dpt.deptno) tmp
where rownum<10)
where rn>0;


select * from (
select tmp.*,rownum rn from (
select emp.*,Arial; line-height:26px"> select * from (
select tmp.*,scott.dept dpt
where emp.deptno=dpt.deptno order by emp.empno) tmp
where rownum<10)
where rn>0;

where emp.deptno=dpt.deptno order by emp.empno) tmp
where rownum<10)
where rn>0;

--下面两个语句返回的记录不一样:
@H_301_4@sql> set linesize 300
sql> set autotrace on
sql>
sql> select * from (
2 select tmp.*,rownum rn from (
3 select /*+ use_nl(emp dpt) */ emp.*,scott.dept dpt
4 where emp.deptno=dpt.deptno) tmp
5 where rownum<10)
6 where rn>0;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 ACCOUNTING 1
7839 KING PRESIDENT 17-NOV-81 5000 10 ACCOUNTING 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 ACCOUNTING 3
7369 SMITH CLERK 7902 17-DEC-80 800 20 RESEARCH 4
7566 JONES MANAGER 7839 02-APR-81 2975 20 RESEARCH 5
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 RESEARCH 6
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 RESEARCH 7
7902 FORD ANALYST 7566 03-DEC-81 3000 20 RESEARCH 8
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 SALES 9

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 728857640

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 981 | 5 (0)| 00:00:01 |
|* 1 | VIEW | | 9 | 981 | 5 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS | | 9 | 450 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 3 | 111 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------


sql> select * from (
2 select tmp.*,rownum rn from (
3 select emp.*,Arial; line-height:26px"> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 RESEARCH 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 SALES 2
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 SALES 3
7566 JONES MANAGER 7839 02-APR-81 2975 20 RESEARCH 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 SALES 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 SALES 6
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 ACCOUNTING 7
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 RESEARCH 8
7839 KING PRESIDENT 17-NOV-81 5000 10 ACCOUNTING 9


Execution Plan
----------------------------------------------------------
Plan hash value: 1914590424

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 981 | 5 (20)| 00:00:01 |
|* 1 | VIEW | | 9 | 981 | 5 (20)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN | | 9 | 450 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 12 | 444 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

--下面两个语句返回的记录一样,因为有了order by emp.empno,这个能保证记录的顺序:
@H_301_4@
sql> select * from (
2 select tmp.*,scott.dept dpt
4 where emp.deptno=dpt.deptno order by emp.empno) tmp
5 where rownum<10)
6 where rn>0;


Execution Plan
----------------------------------------------------------
Plan hash value: 1915320968

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 981 | 6 (17)| 00:00:01 |
|* 1 | VIEW | | 9 | 981 | 6 (17)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 14 | 1344 | 6 (17)| 00:00:01 |
|* 4 | SORT ORDER BY STOPKEY| | 14 | 700 | 6 (17)| 00:00:01 |
| 5 | NESTED LOOPS | | 14 | 700 | 5 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 4 | 148 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------


Execution Plan
----------------------------------------------------------
Plan hash value: 3275588944

--------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 981 | 6 (34)| 00:00:01 | |* 1 | VIEW | | 9 | 981 | 6 (34)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 14 | 1344 | 6 (34)| 00:00:01 | |* 4 | SORT ORDER BY STOPKEY| | 14 | 700 | 6 (34)| 00:00:01 | |* 5 | HASH JOIN | | 14 | 700 | 5 (20)| 00:00:01 | | 6 | TABLE ACCESS FULL | DEPT | 4 | 52 | 2 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | EMP | 14 | 518 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------

猜你在找的Oracle相关文章