all_rows和first_rows区别

SQL> ed
Wrote file afiedt.buf

1* select /*+ all_rows */ * from (select /*+ all_rows */ a.*,rownum r from (select /*+ all_rows */ owner,object_name,created from t where owner = 'SYS' order by object_name) a where rownum < 10 ) where r > 1
SQL> /

OWNER OBJECT_NAME CREATED R
------------------------------------------------------------------------------------------ ------------------------------ ------------------ ----------
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 2
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 3
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 4
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 5
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 6
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 7
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 8
SYS /1005bd30_LnkdConstant 30-JUN-05 9

8 rows selected.

Elapsed: 00:00:00.13

Execution Plan
----------------------------------------------------------
Plan hash value: 882605040

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 504 | | 1447 (2)| 00:00:18 |
|* 1 | VIEW | | 9 | 504 | | 1447 (2)| 00:00:18 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 21917 | 920K| | 1447 (2)| 00:00:18 |
|* 4 | SORT ORDER BY STOPKEY| | 21917 | 834K| 2248K| 1447 (2)| 00:00:18 |
|* 5 | TABLE ACCESS FULL | T | 21917 | 834K| | 1222 (2)| 00:00:15 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R">1)
2 - filter(ROWNUM<10)
4 - filter(ROWNUM<10

)
5 - filter("OWNER"='SYS')

SQL> ed
Wrote file afiedt.buf

1* select /*+ first_rows(10) */ * from (select /*+ first_rows(10) */ a.*, rownum r from (select /*+ first_rows(10) */ owner,object_name,created from t where owner = 'SYS' order by object_name ) a where rownum < 10 ) where r > 1
SQL> /

OWNER OBJECT_NAME CREATED R
------------------------------------------------------------------------------------------ ------------------------------ ------------------ ----------
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 2
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 3
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 4
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 5
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 6
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 7
SYS /1000e8d1_LinkedHashMapValueIt 30-JUN-05 8
SYS /1005bd30_LnkdConstant 30-JUN-05 9

8 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1782234674

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 504 | 14 (0)| 00:00:01 |
|* 1 | VIEW | | 9 | 504 | 14 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 11 | 473 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 11 | 429 | 14 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_OWNER_ONAME_IDX | | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Info

rmation (identified by operation id):
---------------------------------------------------

1 - filter("R">1)
2 - filter(ROWNUM<10)
5 - access("OWNER"='SYS')

SQL>