同事遇到一个奇怪的问题，在使用下面分页导记录时发现分页后的记录跟总的记录数匹配（总的记录数大概25000），但是有些记录不存在,有些重复：@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@变化了，又归到了那个区间
在原有的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 | ---------------------------------------------------------------------------------
