【IT168 技术】在Oracle Database 12c之前,翻页查询需要试用rownum的方式进行SQL嵌套查询编写,这非常复杂,在12c中,新增了Top N查询支持特性,允许试用Offset / Limit 等限定进行Top N查询,原来的ROWNUM方式可以被替代。
其语法结构大致如下:
OFFSET [ROW | ROWS]
FETCH [FIRST | NEXT]
[ | PERCENT] [ROW | ROWS]
[ONLY | WITH TIES]
下图是文档语法结构图:
如果仅返回TOP 5%的记录,下图中的对比可以看到新语法和原有的查询语句之间的巨大差异:
这个新的功能在内部是通过ROW_NUMBER分析函数来实现的,语法类似如下查询,返回经过排序的TOP 5数据内容:
SQL>selectobject_id,object_name from eygle order by object_id2fetch first5rows only;
OBJECT_ID OBJECT_NAME------------------------------------------------------------2C_OBJ#3I_OBJ#4TAB$5CLU$6C_TS#
计划,可以看到,其使用的内部函数是ROW_NUMBER() 分析函数:
SQL>setautotraceonSQL>setlinesize150SQL>selectobject_id,object_name from eygle order by object_id2fetch first5rows only;
Execution Plan----------------------------------------------------------Plan hash value:3708596342------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time|------------------------------------------------------------------------------------------|0|SELECTSTATEMENT | |10000| 1025K| |118(1)|00:00:02|
|*1| VIEW | |10000| 1025K| |118(1)|00:00:02|
|*2| WINDOW SORT PUSHED RANK| |10000| 224K| 328K|118(1)|00:00:02|
|3| TABLE ACCESS FULL | EYGLE |10000| 224K| |46(0)|00:00:01|------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1-filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5)2-filter(ROW_NUMBER() OVER ( ORDER BY"OBJECT_ID")<=5)
Oracle的这一新特性是被期望已久的,这将结果长久以来程序员们极为头痛的翻页算法,福音所在!