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 } ]

{ ROW | ROWS } { ONLY | WITH TIES } ]

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.  

Select * from LOCATION:

ID STATE_NAME
---------- ----------
1 Chicago
2 California
3 Illinois

4 Colorado
5 Florida
6 Alaska

SQL> select * from LOCATION fetch first 1 row only;

ID STATE_NAME
---------- ----------
1 Chicago

Retrieves only first row……

SQL> select * from LOCATION order by ID ASC fetch first 3 rows only;
ID STATE_NAME
---------- ----------
1 Chicago
2 California
3 Illinois

3 ROWS SELECTED

Retrieves first 3 rows from the beginning…

SQL> select * from LOCATION order by ID ASC fetch first 50 percent rows only;
ID STATE_NAME
---------- ----------
1 Chicago
2 California
3 Illinois

3 ROWS SELECTED

Offset Clause. It’s optional; this tells oracle the number of rows that must be skipped from the beginning of the result set. Offset Clause must be followed by ROW or ROWS which is for readability. If the offset is higher than the number of rows retrieved or is NULL, no rows are returned.

SQL> select * from LOCATION order by ID ASC  offset 3 rows fetch next 3 rows only;
ID STATE_NAME
---------- ----------
4 Colorado
5 Florida
6 Alaska.

3 ROWS SELECTED

Retrieves first 3 rows after 3^rd row.The starting point for the FETCH is OFFSET+1.

With TIES specifies to return all rows that have the same sort keys as the last row of the row-limited result set (requires an ORDER BY clause).

Note:Row limiting Clause is followed by Order by Clause in Select Statement, though it’s not mandatory.

There are few more interesting features that have come in Oracle12c which we will discuss in our upcoming posts J