Sunday, 11 May 2014

Oracle Database 12C New Feature-Fetch& Offset

In this post we will discuss about a new feature that was introduced in Oracle 12c to limit the rows returned in an easier and effective manner compared to methods used in previous releases by using ROWNUM or using Top-N rows or using analytic functions like RANK or DENSE_RANK. Using this Feature  Oracle 12c provides improved support for top-n analysis.
This new Row limiting Clause in Oracle allows us to select the Top N like queries without much effort. Let’s take a look into the syntax now.

SELECT * FROM Table_Name
ORDER BY column_name DESC
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]


Features of The New Row limiting Clause In Oracle

Fetch Clause is to limit the number of rows returned in the result set. Fetch Keyword is followed by FIRST or NEXT which can be used interchangeably and is for semantic clarity only. Fetch clause can specify the number of rows or a percentage of rows to return which we will see by using some examples.  

Related Posts Plugin for WordPress, Blogger...