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

Posted

August 10, 2012

mac

Tags:

Comments

One response to “12c分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句”

Maclean Liu

January 11, 2013

In Oracle Database 12c Release 1, SQL SELECT syntax has been enhanced to allow a row limiting clause, which limits the number of rows that are returned in the result set.
Limiting the number or rows returned can be valuable for reporting, analysis, data browsing, and other tasks. Queries that order data and then limit row output are widely used and are often referred to as Top-N queries.
You can specify the number of rows or percentage of rows to return with the FETCH FIRST/NEXT keywords. You can use the OFFSET keyword to specify that the returned rows begin with a row after the first row of the full result set.

You specify the row limiting clause in the SQL SELECT statement by placing it after the ORDER BY clause. Note that an ORDER BY clause is not required.
OFFSET: Use this clause to specify the number of rows to skip before row limiting begins. The value for offset must be a number. If you specify a negative number, offset is treated as 0. If you specify NULL or a number greater than or equal to the number of rows that are returned by the query, 0 rows are returned.
ROW | ROWS: Use these keywords interchangeably. They are provided for semantic clarity.
FETCH: Use this clause to specify the number of rows or percentage of rows to return.
FIRST | NEXT: Use these keywords interchangeably. They are provided for semantic clarity.
row_count | percent PERCENT: Use row_count to specify the number of rows to return. Use percent PERCENT to specify the percentage of the total number of selected rows to return. The value for percent must be a number.
The first code example returns the five employees with the lowest employee_id.
The second code example returns the five employees with the next set of lowest employee_id.

Reply

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

Comments

One response to “12c分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句”

Leave a Reply Cancel reply