As the number of rows you "skip" grows, the amount of work we have to do grows with it. It's based on the principle that it's unlikely for someone to scroll down repeatedly....especially up to 2million rows !
Here's an example
SQL> create table t as
2 select rownum r, d.* from dba_Objects d,
3 ( select 1 from dual connect by level <= 20 );
Table created.
SQL> create or replace
2 procedure searcher(p_offset int) is
3 x int;
4 delta int;
5 s timestamp := systimestamp;
6 begin
7 select sum(value) into delta
8 from v$mystat
9 where statistic# in (14, -- session logical reads
10 141, -- physical reads
11 144); -- physical reads direct
12
13 for i in (
14 select *
15 from (
16 select rownum r1, x.*
17 from
18 ( select * from t order by r ) x
19 where rownum <= p_offset + 100
20 )
21 where r1 > p_offset
22 )
23 loop
24 x := i.object_id;
25 end loop;
26
27 select sum(value)-delta into delta
28 from v$mystat
29 where statistic# in (14, -- session logical reads
30 141, -- physical reads
31 144); -- physical reads direct
32
33 dbms_output.put_line('Work: '||delta);
34 dbms_output.put_line('Time: '||(systimestamp-s));
35
36 end;
37 /
Procedure created.
SQL>
SQL> set serverout on
SQL> exec searcher(1);
Work: 102995
Time: +000000000 00:00:00.669000000
PL/SQL procedure successfully completed.
SQL> exec searcher(1000);
Work: 102995
Time: +000000000 00:00:00.637000000
PL/SQL procedure successfully completed.
SQL> exec searcher(10000);
Work: 102995
Time: +000000000 00:00:00.680000000
PL/SQL procedure successfully completed.
SQL> exec searcher(1000000);
Work: 138879
Time: +000000000 00:00:04.882000000
PL/SQL procedure successfully completed.
So you can see that things were ok until we wanted to offset 1,000,000 rows and then we had a big dip in speed. So I trace the last two executions, and you can see why:
--
-- 100000 rows
--
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 VIEW (cr=34335 pr=34330 pw=0 time=1203304 us cost=61556 size=771144680 card=1957220)
100100 100100 100100 COUNT STOPKEY (cr=34335 pr=34330 pw=0 time=1177001 us)
100100 100100 100100 VIEW (cr=34335 pr=34330 pw=0 time=1155986 us cost=61556 size=745700820 card=1957220)
100100 100100 100100 SORT ORDER BY STOPKEY (cr=34335 pr=34330 pw=0 time=1133345 us cost=61556 size=232909180 card=1957220)
1957220 1957220 1957220 TABLE ACCESS FULL T (cr=34335 pr=34330 pw=0 time=398648 us cost=9396 size=232909180 card=1957220)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.02 0.02
direct path read 9 0.00 0.00
********************************************************************************
--
-- 1000000 rows
--
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 VIEW (cr=34335 pr=52271 pw=17941 time=3927721 us cost=61556 size=771144680 card=1957220)
1000100 1000100 1000100 COUNT STOPKEY (cr=34335 pr=52271 pw=17941 time=3709735 us)
1000100 1000100 1000100 VIEW (cr=34335 pr=52271 pw=17941 time=3544977 us cost=61556 size=745700820 card=1957220)
1000100 1000100 1000100 SORT ORDER BY STOPKEY (cr=34335 pr=52271 pw=17941 time=3361620 us cost=61556 size=232909180 card=1957220)
1957220 1957220 1957220 TABLE ACCESS FULL T (cr=34335 pr=34330 pw=0 time=921024 us cost=9396 size=232909180 card=1957220)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.01 0.03
direct path read 117 0.04 0.52
direct path write temp 393 0.03 0.67
direct path read temp 805 0.01 0.36
********************************************************************************
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.01 0.03
direct path read 117 0.04 0.52
direct path write temp 393 0.03 0.67
direct path read temp 805 0.01 0.36
********************************************************************************
Notice we cross a threshold where we had to start spilling some rows to temporary storage (direct path write temp) because we had to hold on to a big chunk of rows.
If you can index the sorting column in question, then you can avoid this, but of course, if its a "free format" style of query, ie, where uses can nominate all sorts of predicates, then that's probably not going to fly. The question remains - do people really want to scroll down through 2million records ?