ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 使用rownum进行分页的陷阱(2)

oracle 使用rownum进行分页的陷阱(2)

原创 Linux操作系统 作者:aaqwsh 时间:2011-06-09 11:14:24 0 删除 编辑

本来想一起发的,老是报错,不让提交,内容太长?。

四个测试语句(增加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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    265298