oracle 使用rownum进行分页的陷阱

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

SELECT*

FROM(SELECTrow_.*,rownumrn

FROM(select--省略几百行

orderbysdate,mname,blevelasc) row_

WHERErownum<=10000)

WHERERN >0

WHERErownum<=20000)

WHERERN > 10000

WHERErownum<=30000)

WHERERN > 20000

可能原因：

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

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

能解释的现象：

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

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

解决方法：

在原有的3个排序字段后，增加一个唯一性的排序字段（如no），当然，这是有代价的：

order by sdate,blevel asc,no

关于rownum是怎么产生的（网上有不少的文章，下面是摘录）：

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

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

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

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

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

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

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

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;

--下面两个语句返回的记录不一样：
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，这个能保证记录的顺序：

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 使用rownum进行分页的陷阱

相关文章