原理：ROWNUM是一个虚字段，只有产生结果集时才会有值，步骤为：
1 Oracle executes your query.
执行查询操作
2 Oracle fetches the first row and calls it row number 1.
将第一行的row num置为1
3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.
将得到的行的row num与条件相比较，如果不匹配，则抛弃行，如果匹配，则返回行
4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
oracle获取下一行，然后将rownum增1
5 Go to step 3.
返回第3步
按照原理推算下列结果：
select rownum,object_name from
t where rownum>=5;--没有结果
select rownum,object_name from
t where rownum=1;--返回第1行
select rownum,object_name from
t where rownum !=10;--返回1-9行
select rownum,object_name from
t where rownum >=1;--返回全部行,>0,>=0一样
根据rownum进行topn的输出性能比较：
SQL> select *
2 from (select t.*, rownum rn from t) b
3 where b.rn between 6 and 10;
执行计划
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57610 | 10M| 165 (4)| 00:00:02 |
|* 1 | VIEW | | 57610 | 10M| 165 (4)| 00:00:02 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| T | 57610 | 9957K| 165 (4)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."RN"<=10 AND "B"."RN">=6)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
710 consistent gets
0 physical reads
0 redo size
1475 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> select * from t where rownum <=10
2 minus
3 select * from t where rownum <=5;
执行计划
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2655 | | 4818 (51)| 00:00:58 |
| 1 | MINUS | | | | | | |
| 2 | SORT UNIQUE | | 10 | 1770 | 23M| 2409 (1)| 00:00:29 |
|* 3 | COUNT STOPKEY | | | | | | |
| 4 | TABLE ACCESS FULL| T | 57610 | 9957K| | 165 (4)| 00:00:02 |
| 5 | SORT UNIQUE | | 5 | 885 | 23M| 2409 (1)| 00:00:29 |
|* 6 | COUNT STOPKEY | | | | | | |
| 7 | TABLE ACCESS FULL| T | 57610 | 9957K| | 165 (4)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=10)
6 - filter(ROWNUM<=5)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1402 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> select * from (
2 select a.*,rownum rn from t a where rownum <=10
3 )b where b.rn >5;
执行计划
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1900 | 5 (60)| 00:00:01 |
|* 1 | VIEW | | 10 | 1900 | 5 (60)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| T | 57610 | 9957K| 5 (60)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."RN">5)
2 - filter(ROWNUM<=10)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1475 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed