Gimmick with offset and fetch N rows first in 12c

I was looking into some new features of 12c and I didn’t need to dig deep, in order to figure out how does the “fetch first” feature work:

QL> set autotrace on
SQL> select ename from emp offset 5 rows fetch next 5 rows only;

ENAME
———-
BLAKE
CLARK
SCOTT
KING
TURNER

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 3611411408

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 280 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 280 | 2 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 14 | 84 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“from$_subquery$_002″.”rowlimit_$$_rownumber”<=CASE WHEN
(5>=0) THEN 5 ELSE 0 END +5 AND “from$_subquery$_002″.”rowlimit_$$
_rownu

mber”>5)
2 – filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=CASE WHEN (5>=0)
THEN 5 ELSE 0 END +5)

So, there is “ROW_NUMBER analytic function that I have used before to do the same thing. In other words, the long awaited new feature is just slightly simplified old trick with ROW_NUMBER() function. And “VIEW” in the plan tells me that my query was rewritten, as there were no views in my query. Nevertheless, this new feature will come in very handy, for everybody writing a web application and in need of efficient pagination method. One disappointing thing is that “FIRST” and “NEXT” are synonymous and return the same thing. In other words, statements will not remember their offset.
Unfortunately, the associated PL/SQL fetch statement doesn’t have an offset clause, only the well known limit clause:

declare
cursor csr is select ename from emp;
type tab_emp is table of emp.ename%type;
var_temp tab_emp:=tab_emp();
begin
open csr;
fetch csr bulk collect into var_temp limit 5;
for i in var_temp.first..var_temp.last
loop
dbms_output.put_line(var_temp(i));
end loop;
end;
/

If you try to put OFFSET clause anywhere in the FETCH command, a syntax error will follow. That means that it is not possible to skip first 3 pages and fetch just the forth one. The application needs to fetch the first 3 pages, in order to be able to display the 4th page.

 

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s