ORACLE 12C TOP N SQL实现分页功能

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORACLE 12C TOP N SQL实现分页功能

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

从oracle 12c开始,oracle 也提供了类似sql server的top,mysql的limit分页功能,在本文中分别通过TOP N和传统方法来实现分页,sql实现效果是(按照id 倒序排列,取第六条到底十条)
TOP N写法

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> set autot on exp stat
SQL> SELECT id
  2  FROM t_xifenfei
  3  ORDER BY id  desc offset 5 rows FETCH next 5 ROWS ONLY;

        ID
----------
       188
       187
       186
       185
       184


Execution Plan
----------------------------------------------------------
Plan hash value: 755690401

---------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |   192 |  7488 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |            |   192 |  7488 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|            |   192 |   768 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_XIFENFEI |   192 |   768 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN (5>=0)
              THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ID") DESC
              )<=CASE  WHEN (5>=0) THEN 5 ELSE 0 END +5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

传统分页写法

SQL> select id from (select id,rownum rn from (
  2  select id  from t_xifenfei order by id desc
  3  )) where rn<11 and rn>=6;

        ID
----------
       188
       187
       186
       185
       184


Execution Plan
----------------------------------------------------------
Plan hash value: 327151993

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   192 |  4992 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                 |            |   192 |  4992 |     3   (0)| 00:00:01 |
|   2 |   COUNT               |            |       |       |            |          |
|   3 |    VIEW               |            |   192 |  2496 |     3   (0)| 00:00:01 |
|   4 |     SORT ORDER BY     |            |   192 |   768 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T_XIFENFEI |   192 |   768 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=6 AND "RN"<11)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

从这里可以看出来两种sql分页写法,在小数据量上效率都差不多,但是明显TOP N的写法更加简单,更加灵活.如果数据量大可能还是需要自己写分页SQL。TOP N是通过ROW_NUMBER() OVER INTERNAL_FUNCTION(“ID”)和CASE WHEN内部转换实现分页功能.

此条目发表在 ORACLE 12C 分类目录。将固定链接加入收藏夹。

ORACLE 12C TOP N SQL实现分页功能》有 2 条评论

  1. 有教无类 说:

    FETCH新特性用不上stopkey功能啊

  2. 惜分飞 说:

    参数说明

    OFFSET
    Use this clause to specify the number of rows to skip before row limiting begins.
    offset must be a number. If you specify a negative number, then offset is treated as
    0. If you specify NULL, or a number greater than or equal to the number of rows
    returned by the query, then 0 rows are returned. If offset includes a fraction, then the
    fractional portion is truncated. If you do not specify this clause, then offset is 0 and
    row limiting begins with the first row.
    
    ROW | ROWS 
    These keywords can be used interchangeably and are provided for
    semantic clarity.
    
    FETCH
    Use this clause to specify the number of rows or percentage of rows to return. If you
    do not specify this clause, then all rows are returned, beginning at row offset + 1.
    
    FIRST | NEXT 
    These keywords can be used interchangeably and are provided for
    semantic clarity.
    rowcount | percent PERCENT Use rowcount to specify the number of rows to return.
    rowcount must be a number. If you specify a negative number, then rowcount is
    treated as 0. If rowcount is greater than the number of rows available beginning at row
    offset + 1, then all available rows are returned. If rowcount includes a fraction, then
    the fractional portion is truncated. If rowcount is NULL, then 0 rows are returned.
    Use percent PERCENT to specify the percentage of the total number of selected rows to
    return. percent must be a number. If you specify a negative number, then percent is
    treated as 0. If percent is NULL, then 0 rows are returned.
    If you do not specify rowcount or percent PERCENT, then 1 row is returned.
    
    ROW | ROWS 
    These keywords can be used interchangeably and are provided for
    semantic clarity.
    
    ONLY | WITH TIES 
    Specify ONLY to return exactly the specified number of rows or
    percentage of rows. This is the default.
    Specify WITH TIES to return additional rows with the same sort key as the last row
    fetched. If you specify WITH TIES, then you must specify the order_by_clause. If you
    do not specify the order_by_clause, then no additional rows will be returned.