oracle 使用rownum進行分頁的陷阱

同事遇到一個奇怪的問題，在使用下面分頁導記錄時發現分頁後的記錄跟總的記錄數匹配（總的記錄數大概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--20000，20000--30000的sql執行計劃不同，造成結果集記錄的順序不同(這個也許有可能，沒有驗證過)。

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的初始值為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是為了打亂結果集裡記錄順序，類似模擬select  order by xx 多次執行記錄的順序不一樣）

 

如下的語句如果按emp.job排序取前八條記錄，7698 BLAKE，7566 JONES，7782 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/，如需轉載，請註明出處，否則將追究法律責任。