本来想一起发的，老是报错，不让提交，内容太长？。
四个测试语句（增加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博客 ” ，链接：https://blog.itpub.net/758322/viewspace-697460/ ，如需转载，请注明出处，否则将追究法律责任。
