ANBOB™

在分页查询或top n中在oracle 之间的版本使用rownum,row_number..，从12c起 提供了offset fetch 的语法，其实这个语法不是什么新鲜玩意，在DB2,MSSQL,MYSQL,PostgreSQL之前也都提供的语法。

MS SQL 2012 Syntax

\\[ ORDER BY 

\\[ OFFSET  \\{ ROW | ROWS \\}

     \\[ FETCH \\{ FIRST | NEXT \\}  \\{ ROW | ROWS \\} ONLY \\] \\] \\]

ORACLE 12 C Syntax

OFFSET { integer-literal | ? } {ROW | ROWS}

FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY

ROW is synonymous with ROWS and FIRST is synonymous with NEXT.
For the result offset clause the integer literal (or dynamic parameter) must be equal to 0 (default if the clause is not given), or positive. If it is larger than the number of rows in the underlying result set, no rows are returned.
For the fetch first clause, the literal (or dynamic parameter) must be 1 or higher. The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.

Both MySQL and PostgreSQL Syntax

mysql> select * from temp_fen limit 10 offset 10;
+------+-----------+
| h    | code      |
+------+-----------+
|  3.0 | 110110046 |
| 15.0 | 11011006I |
|  9.0 | 110110074 |
|  9.0 | 11011007Q |
|  3.0 | 110110096 |
| 15.0 | 11011009L |
|  9.0 | 1101100A7 |
|  9.0 | 110111H08 |
|  6.0 | 110111H1G |
|  9.0 | 110111H2M |
+------+-----------+

下面展示一下oracle 12c中的使用,如TOP N 行，从N行起向后N行, TOP 百分比..

anbob@PDB1>select * from v$version;

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


anbob@PDB1>create table testpage as  select rownum id, lpad(chr(30+rownum),20,'bob') name from dual connect by level<=100;
Table created.

anbob@PDB1>select * from testpage order by id fetch first rows only;
                  ID NAME
-------------------- ----------------------------------------
                   1 bobbobbobbobbobbobb

anbob@PDB1>select * from testpage order by id fetch first 5 rows only;
                  ID NAME
-------------------- ----------------------------------------
                   1 bobbobbobbobbobbobb
                   2 bobbobbobbobbobbobb
                   3 bobbobbobbobbobbobb!
                   4 bobbobbobbobbobbobb"
                   5 bobbobbobbobbobbobb#
				   
anbob@PDB1>select * from testpage order by id offset 5 rows fetch next 5 rows only;
                  ID NAME
-------------------- ----------------------------------------
                   6 bobbobbobbobbobbobb$
                   7 bobbobbobbobbobbobb%
                   8 bobbobbobbobbobbobb&
                   9 bobbobbobbobbobbobb'
                  10 bobbobbobbobbobbobb(
				  
anbob@PDB1>set autot trace exp 
anbob@PDB1>select * from testpage order by id offset 5 rows fetch next 5 rows only;

Execution Plan
----------------------------------------------------------
Plan hash value: 742818132

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |   100 |  6100 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |          |   100 |  6100 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|          |   100 |  2400 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | TESTPAGE |   100 |  2400 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

anbob@PDB1>select * from (select t.*,row_number() over(order by id) rn from testpage t) where rn between 6 and 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 742818132

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |   100 |  4800 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |          |   100 |  4800 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|          |   100 |  2400 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | TESTPAGE |   100 |  2400 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">=6 AND "RN"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=10)

anbob@PDB1>select * from testpage order by id fetch first 5 percent rows only;
                  ID NAME
-------------------- ----------------------------------------
                   1 bobbobbobbobbobbobb
                   2 bobbobbobbobbobbobb
                   3 bobbobbobbobbobbobb!
                   4 bobbobbobbobbobbobb"
                   5 bobbobbobbobbobbobb#

anbob@PDB1>select * from testpage order by id offset 10 rows fetch first 5 percent rows only;
                  ID NAME
-------------------- ----------------------------------------
                  11 bobbobbobbobbobbobb)
                  12 bobbobbobbobbobbobb*
                  13 bobbobbobbobbobbobb+
                  14 bobbobbobbobbobbobb,
                  15 bobbobbobbobbobbobb-

anbob@PDB1>delete testpage where id>50;

50 rows deleted.

anbob@PDB1>select * from testpage order by id offset 10  rows fetch first 20 percent rows only;

                  ID NAME
-------------------- ----------------------------------------
                  11 bobbobbobbobbobbobb)
                  12 bobbobbobbobbobbobb*
                  13 bobbobbobbobbobbobb+
                  14 bobbobbobbobbobbobb,
                  15 bobbobbobbobbobbobb-
                  16 bobbobbobbobbobbobb.
                  17 bobbobbobbobbobbobb/
                  18 bobbobbobbobbobbobb0
                  19 bobbobbobbobbobbobb1
                  20 bobbobbobbobbobbobb2

10 rows selected.

anbob@PDB1>select * from testpage order by id fetch first 20 percent rows only;

                  ID NAME
-------------------- ----------------------------------------
                   1 bobbobbobbobbobbobb
                   2 bobbobbobbobbobbobb
                   3 bobbobbobbobbobbobb!
                   4 bobbobbobbobbobbobb"
                   5 bobbobbobbobbobbobb#
                   6 bobbobbobbobbobbobb$
                   7 bobbobbobbobbobbobb%
                   8 bobbobbobbobbobbobb&
                   9 bobbobbobbobbobbobb'
                  10 bobbobbobbobbobbobb(

Summary:
个人感觉还没有row_number好，fetch offset 执行计划也是类似的窗口函数，不过对开发的代码量是有所减少，也可能是为了和其它数据统一标准。

打赏

微信扫一扫，打赏作者吧～