联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
从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内部转换实现分页功能.
FETCH新特性用不上stopkey功能啊
参数说明