|
同事遇到一个奇怪的问题，在使用下面分页导记录时发现分页后的记录跟总的记录数匹配（总的记录数大概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 | --------------------------------------------------------------------------------- （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
