I am on 10.2.0.1 and found an very interesting implementation in oracle having row_number() analytical function optimized for Top-n-queries. Here is sample demo.
drop table t1 purge;
drop table t2 purge;
create table t1
nologging as
select * from all_objects;
create table t2
nologging as
select * from t1;
alter table t1
add constraint t1_pk
primary key(object_id);
alter table t2
add constraint t2_pk
primary key(object_id);
begin
dbms_stats.gather_table_stats(user,'T1',cascade=>true);
dbms_stats.gather_table_stats(user,'T2',cascade=>true);
end;
/
variable l_start_id number;
variable l_end_id number;
exec :l_start_id := 75; :l_end_id := 100;
Traditional way of doing pagination is using the below query.
rajesh@ORA10G> select *
2 from (
3 select t.*, rownum r
4 from (
5 select /*+ first_rows(25) */ *
6 from t1
7 order by object_name,object_id
8 ) t
9 where rownum <= :l_end_id
10 )
11 where r >= :l_start_id
12 /
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2771300550
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50130 | 6902K| | 1243 (2)| 00:00:15 |
|* 1 | VIEW | | 50130 | 6902K| | 1243 (2)| 00:00:15 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 50130 | 6266K| | 1243 (2)| 00:00:15 |
|* 4 | SORT ORDER BY STOPKEY| | 50130 | 4552K| 12M| 1243 (2)| 00:00:15 |
| 5 | TABLE ACCESS FULL | T1 | 50130 | 4552K| | 161 (3)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R">=TO_NUMBER(:L_START_ID))
2 - filter(ROWNUM<=TO_NUMBER(:L_END_ID))
4 - filter(ROWNUM<=TO_NUMBER(:L_END_ID))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.33 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 0 695 0 26
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.35 0 695 0 26
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
26 VIEW (cr=695 pr=0 pw=0 time=23070 us)
100 COUNT STOPKEY (cr=695 pr=0 pw=0 time=23119 us)
100 VIEW (cr=695 pr=0 pw=0 time=23117 us)
100 SORT ORDER BY STOPKEY (cr=695 pr=0 pw=0 time=23116 us)
50130 TABLE ACCESS FULL T1 (cr=695 pr=0 pw=0 time=38 us)
And the analytics version of pagination is here, which does exactly the same work by pushing down the predicates.
rajesh@ORA10G> select *
2 from (
3 select /*+ first_rows(25) */ t1.* ,
4 row_number() over(order by object_name,object_id) r
5 from t1
6 )
7 where r between :l_start_id and :l_end_id
8 /
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2529418927
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50130 | 6902K| | 1243 (2)| 00:00:15 |
|* 1 | VIEW | | 50130 | 6902K| | 1243 (2)| 00:00:15 |
| 2 | WINDOW SORT | | 50130 | 4552K| 12M| 1243 (2)| 00:00:15 |
|* 3 | FILTER | | | | | | |
| 4 | TABLE ACCESS FULL| T1 | 50130 | 4552K| | 161 (3)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R">=TO_NUMBER(:L_START_ID) AND "R"<=TO_NUMBER(:L_END_ID))
3 - filter(TO_NUMBER(:L_START_ID)<=TO_NUMBER(:L_END_ID))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.23 0 695 0 26
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.25 0 695 0 26
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
26 VIEW (cr=695 pr=0 pw=0 time=233660 us)
101 WINDOW SORT PUSHED RANK (cr=695 pr=0 pw=0 time=233807 us)
50130 FILTER (cr=695 pr=0 pw=0 time=44 us)
50130 TABLE ACCESS FULL T1 (cr=695 pr=0 pw=0 time=41 us)
