当前位置:Oracle研究中心 > 运维DBA >
时间:2016-06-17 10:14 来源:Oracle研究中心 作者:惜分飞 点击: 次
从oracle 12c开始,oracle 也提供了类似sql server的top,mysql的limit分页功能,在本文中分别通过TOP N和传统方法来实现分页,sql实现效果是(按照id 倒序排列,取第六条到底十条)
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_oracleplus 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_oracleplus | 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_oracleplus 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_oracleplus | 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-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 12C TOP N SQL实现分页功能
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/411.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。