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):
-------------------------------------------