Gimmick with offset and fetch N rows first in 12c

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 19 Jun 2014 03:28:15 +0000 (UTC)
Message-ID: <pan.2014.06.19.03.28.15_at_gmail.com>

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.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Thu Jun 19 2014 - 05:28:15 CEST