12c返回指定行数

如果对返回结果进行行数的过滤时，通常都是通过order by + rownum <的方式，对于返回中间行数的情况就需要通过嵌套分页的写法，而在其他类型的数据库比如Mysql中可以比较方便的直接通过limit的方式

SELECT *
FROM t1
ORDER BY 1 LIMIT 0,
                 10;

row limiting概念

从12c开始，oracle对这方面的功能大大的增强，引入了Row_limiting_clause语句格式，允许查询sql能限制返回的行数以及返回结果集开始的行位置。许多查询sql需要限制返回的行数和修改返回行数的起始位置，比如在返回结果集中只需要过滤top-N的数据，这个时候就只需要FETCH FIRST和OFFSET选项就能简单完成。

Row_limiting_clause语句通过指定起始偏移量，返回行数n或者返回记录数的百分比，将这些选项都接在常规sql的order by语句后面。

它主要通过以下选项来实现：

• OFFSET

  用来指定限制返回行数开始前需要跳过的行数的整数值，如果offset为负数或未指定，则默认为0并且限制返回行数时从第一行开始。如果值为NULL或者大于等于返回行的总数，那么最终返回0行，等于是进行了截断。

• FETCH

  指定返回多少行或者多少百分比的记录，如果不指定，则默认返回所有行，第一行从offset+1开始

• FIRST | NEXT

  这些关键字可以互换使用，根据场景来判断

• Rowcount | percent PERCENT

  使用Rowcount来指定返回的行数，负值或者NULL则被当做0。如果指定的值大于最大返回行数，则只返回可返回的最大行数。percent PERCENT类似，表示返回的百分比。

  如果这两个值都没指定，则默认返回一行。

• ONLY | WITH TIES

  ONLY只返回指定行数的精确值，而WITH TIES则在除了指定行数以外，如果还有与最后一行数据同样的order by值的时候会将其他满足条件的记录都返回，如果使用WITH TIES则必须配合order by语句，否则额外的行都不会返回。

ROW limiting语句的限制：

1. 不能用于for update语句
2. select后面不能包含序列的CURRVAL或NEXTVAL
3. 物化视图如果包含row limiting，则无法刷新

示例

创建一张测试表，插入部分重复数据

DROP TABLE t1 purge;

CREATE TABLE t1 (id number);

INSERT INTO t1
SELECT LEVEL
FROM dual CONNECT BY LEVEL >=5;

INSERT INTO t1
SELECT *
FROM t1;

 COMMIT;

现在t1表则包含10条数据，其中一半是重复的

xb@PDB12C> select * from t1 order by 1; 

        ID
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5

按id排序只取前5行数据

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS ONLY;

        ID
----------
         1
         1
         2
         2
         3

因为ID=3的记录有两条，通过with ties将与第5行id值一样的记录也展示出来

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS WITH ties;

        ID
----------
         1
         1
         2
         2
         3
         3

用percent取id前30%的数据

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS ONLY;

        ID
----------
         1
         1
         2

percent配合with ties

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS WITH ties;

        ID
----------
         1
         1
         2
         2

以前的版本如果要取中间的数据只能用嵌套分页的办法，比如取根据ID排序第3到5行的数据

# before 12c
SELECT id
FROM
  (SELECT rownum rn,
          id
   FROM
     (SELECT id
      FROM t1
      ORDER BY 1)
   WHERE rownum >=5)
WHERE rn >=3;

# after 12c
SELECT id
FROM t1
ORDER BY 1
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

不根据id排序，而是根据rownum取5-7行数据

SELECT rownum,
       id
FROM t1
OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;

    ROWNUM         ID
---------- ----------
         5          5
         6          1
         7          2

效率问题

之前做分页的时候都会碰到id between 5 and 7的写法，我们知道这是很低效的，所有都是通过rownum的嵌套方式来书写，那新的写法执行效率又如何？

对此做一个10053 trace，看下执行计划是如何

oradebug setmypid
oradebug event 10053 trace name context forever,level 12;

SELECT id
FROM xb.t1
ORDER BY 1
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

oradebug event 10053 trace name context off;

sys@ORA12C> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_112949.trc

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."ID" "ID"
FROM
  (SELECT "T1"."ID" "ID",
          "T1"."ID" "rowlimit_$_0",
          ROW_NUMBER() OVER (
                             ORDER BY "T1"."ID") "rowlimit_$$_rownumber"
   FROM "XB"."T1" "T1"
   WHERE 2>CASE WHEN (2>=0) THEN 2 ELSE 0 END +3) "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber">=CASE
                                                         WHEN (2>=0) THEN 2
                                                         ELSE 0
                                                     END +3
  AND "from$_subquery$_002"."rowlimit_$$_rownumber">2
ORDER BY "from$_subquery$_002"."rowlimit_$_0"

----- Plan Table -----

============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id  | Operation                 | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT          |         |       |       |     4 |           |
| 1   |  VIEW                     |         |    10 |   390 |     4 |  00:00:01 |
| 2   |   WINDOW SORT PUSHED RANK |         |    10 |   130 |     4 |  00:00:01 |
| 3   |    TABLE ACCESS FULL      | T1      |    10 |   130 |     3 |  00:00:01 |
--------------------------------------------+-----------------------------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$1                / from$_subquery$_002@SEL$2
 2 - SEL$1
 3 - SEL$1                / T1@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------

*** 2019-08-21T13:50:43.663264+08:00 (PDB12C(3))
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber">=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")>=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3)

可以看到实际上是对sql做了转换，先通过分析函数将伪列的值查出，然后判断OFFSET值是否符合要求，如果大于0则为2，然后返回后面3行。

通过这个大概能猜到如果是with ties的写法，那么可能就是把ROW_NUMBER分析函数换成了RANK。