12c分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句

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写法高出不少，这似乎是由于不同的基数计算导致的。