从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内部转换实现分页功能.
