Gimmick with offset and fetch N rows first in 12c
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 |00:00:01 |
-------------------------------------------------------------------------------
| 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)|
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.comReceived on Thu Jun 19 2014 - 05:28:15 CEST