Database 12c的FETCH FIRST ROWS特性可以简化老版本中ROW_NUM()或ROWNUM的分页排序写法， 大幅节约开发花在分页查询语句上的时间。
row-limiting子句用以限制某个查询返回的行数
- 可以通过FETCH FIRST/NEXT关键字指定返回结果的行数
- 可以通过PERCENT关键字指定返回结果的行数比例
- 可以通过OFFSET关键字指定返回从结果集中的某一行之后行数
12c row-limiting子句对于排序数据限制返回行今后会广泛使用(MySQL上早就有的特性，MySQL开发该特性可能是特别考虑到对于网站分页查询的简化)，也可以被称作Top-N查询。
示意图：
我们这里来对比老的ROWNUM写法等价的FETCH ROWS写法的实际性能对比：
create table larget tablespace users as select rownum t1, rpad('M',99,'A') t2, rpad('M',99,'A') t3, rpad('M',99,'A') t4 from dual connect by level<=99999;
SQL> create index pk_ind on larget(t1) tablespace users;
Index created.
select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=20) llv
where
llv.rn>=1;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3843929721
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3580 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 20 | 3580 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 20 | 3320 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 3 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 20 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN">=1)
2 - filter(ROWNUM<=20)
5 - filter("T1" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1841 bytes sent via SQL*Net to client
554 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL> select * from larget where t1 is not null order by t1 fetch first 20 rows only;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3254405084
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 18M| 4573 (1)| 00:00:01 |
|* 1 | VIEW | | 99999 | 18M| 4573 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 99999 | 29M| 4573 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 4573 (1)| 00:00:01 |
|* 4 | INDEX FULL SCAN | PK_IND | 99999 | | 224 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "LARGET"."T1")<=20)
4 - filter("T1" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1717 bytes sent via SQL*Net to client
554 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
select llv.* from
(
select rownum rn, ll.* from
(select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll
where rownum<=20000) llv
where
llv.rn>=18000;
2001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3843929721
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 3496K| 916 (0)| 00:00:01 |
|* 1 | VIEW | | 20000 | 3496K| 916 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 20000 | 3242K| 916 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 916 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PK_IND | 20000 | | 46 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LLV"."RN">=18000)
2 - filter(ROWNUM<=20000)
5 - filter("T1" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1177 consistent gets
904 physical reads
0 redo size
56804 bytes sent via SQL*Net to client
2006 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2001 rows processed
select * from larget where t1 is not null order by t1 OFFSET 17999 ROWS FETCH NEXT 2001 ROWS ONLY;
T1
----------
18001
Execution Plan
----------------------------------------------------------
Plan hash value: 3254405084
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 18M| 4573 (1)| 00:00:01 |
|* 1 | VIEW | | 99999 | 18M| 4573 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 99999 | 29M| 4573 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 4573 (1)| 00:00:01 |
|* 4 | INDEX FULL SCAN | PK_IND | 99999 | | 224 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN
(17999>=0) THEN 17999 ELSE 0 END +2001 AND
"from$_subquery$_003"."rowlimit_$$_rownumber">17999)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "LARGET"."T1")<=CASE WHEN
(17999>=0) THEN 17999 ELSE 0 END +2001)
4 - filter("T1" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1177 consistent gets
0 physical reads
0 redo size
46757 bytes sent via SQL*Net to client
2006 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2001 rows processed
不仅可以指定fetch的rows数目，还可以指定返回的行比例，但是这一般会引起真正的INDEX FULL SCAN
SQL> select * from larget where t1 is not null order by t1 fetch first 1 percent rows only;
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 978863371
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 19M| 4573 (1)| 00:00:01 |
|* 1 | VIEW | | 99999 | 19M| 4573 (1)| 00:00:01 |
| 2 | WINDOW BUFFER | | 99999 | 29M| 4573 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 4573 (1)| 00:00:01 |
|* 4 | INDEX FULL SCAN | PK_IND | 99999 | | 224 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CEIL("from$_subquer
y$_003"."rowlimit_$$_total"*1/100))
4 - filter("T1" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4571 consistent gets
3656 physical reads
0 redo size
22863 bytes sent via SQL*Net to client
1269 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
从以上的2个演示对比，可以看到2种写法实际消耗逻辑读数目是类似的；即12c 中FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT的写法在实际性能上并不比传统的rownum写法来的效率低。 但是可以看到CBO在评估fetch rows执行计划的成本时其 TABLE ACCESS BY INDEX ROWID成本要比rownum写法高出不少，这似乎是由于不同的基数计算导致的。
