oracle 使用rownum進行分頁的陷阱(2)

本來想一起發的，老是報錯，不讓提交，內容太長？。

四個測試語句（增加hints是為了打亂結果集裡記錄順序，類似模擬select  order by xx 多次執行記錄的順序不一樣）、執行計劃、結果：
select * from (
select tmp.*,rownum rn from (
select /*+ use_nl(emp dpt) */  emp.*,dpt.dname from scott.emp 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.*,dpt.dname from scott.emp emp ,scott.dept dpt
where emp.deptno=dpt.deptno) tmp
where rownum<10)
where rn>0;

select * from (
select tmp.*,rownum rn from (
select /*+ use_nl(emp dpt) */  emp.*,dpt.dname from scott.emp emp ,scott.dept dpt
where emp.deptno=dpt.deptno order by emp.empno) tmp
where rownum<10)
where rn>0;

select * from (
select tmp.*,rownum rn from (
select   emp.*,dpt.dname from scott.emp emp ,scott.dept dpt
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.*,dpt.dname from scott.emp 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.*,dpt.dname from scott.emp 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
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------- ----------
      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

9 rows selected.

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.*,rownum rn from (
  3  select /*+ use_nl(emp dpt) */  emp.*,dpt.dname from scott.emp emp ,scott.dept dpt
  4  where emp.deptno=dpt.deptno order by emp.empno) tmp
  5  where rownum<10)
  6  where rn>0;

     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

9 rows selected.

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 |
---------------------------------------------------------------------------------

SQL> select * from (
  2  select tmp.*,rownum rn from (
  3  select   emp.*,dpt.dname from scott.emp emp ,scott.dept dpt
  4  where emp.deptno=dpt.deptno order by emp.empno) tmp
  5  where rownum<10)
  6  where rn>0;

     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

9 rows selected.

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 |
---------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-697460/，如需轉載，請註明出處，否則將追究法律責任。