Hello, Ask Tom Team.
I want to do pagination in a report connected to an Oracle 18c database.
I have read there are some methods to do the pagination like: offset and seek. I have several questions
1. What method is the best? I have heard that OFFSET methods can lead to wrong results.
2. Can you write an example of both methods paginating through results?
Thanks in advanced.
Regards,
When you use OFFSET, the database reads all the offset rows plus the N you want to see.
With the seek method, you remember the primary/unique key value for the last row read from the previous page.
For example, say you're paging through rows in batches of 10. By the time you get to the 6th page, offset reads 60 rows - the 50 from the previous five pages and 10 for the current page:
create table t ( c1 primary key, c2, c3 ) as
select level c1,
mod ( level, 2 ) c2,
rpad ( 'stuff', 500, 'f' ) c3
from dual
connect by level <= 100;
set serveroutput off
alter session set statistics_level = all;
select * from t
order by c1
offset 50 rows
fetch next 10 rows only;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |
|* 1 | VIEW | | 1 | 60 | 10 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 60 | 60 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 100 | 60 |
| 4 | INDEX FULL SCAN | SYS_C0022223 | 1 | 60 | 60 |
---------------------------------------------------------------------------------
Notice 60 rows in the A-rows column for lines 2-4 in the plan.
Whereas with the seek method, you read remember that the last value on page 5 was 50. So you search for rows where c1 > 50 and get the next 10:
select * from t
where c1 > 50
order by c1
fetch first 10 rows only;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |
|* 1 | VIEW | | 1 | 10 | 10 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 | 10 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 10 |
|* 4 | INDEX RANGE SCAN | SYS_C0022223 | 1 | | 10 |
---------------------------------------------------------------------------------
Just 10 rows read in total. So as you page further through the results, OFFSET FETCH NEXT will get progressively slower. But the seek method takes a similar amount of time.
You can only use the seek method when people page through results one-by-one. If I skip straight from page one to page six, you don't know the last value on the fifth page. Because you haven't visited it!
In this case you can combine both techniques. Start from the last value on page one. But offset to the 6th page:
select * from t
where c1 > 10
order by c1
offset 40 rows
fetch next 10 rows only;
It's worth noting that BOTH methods can lead to "wrong" results.
With OFFSET FETCH NEXT, if another session inserts rows that sort on an earlier page, you'll see duplicate rows:
select c1 from t
order by c1
fetch first 5 rows only;
C1
1
2
3
4
5
insert into t values ( 0, 0, 'new' );
select c1 from t
order by c1
offset 5 rows
fetch next 5 rows only;
C1
5 <- 5 again!
6
7
8
9
select c1 from t
where c1 > 5
order by c1
fetch first 5 rows only;
C1
6
7
8
9
10
If, between fetches, another session updates a row that didn't appear on page 1 so it now would, then you'll never see this row with both methods:
rollback;
select c1 from t
where c2 = 0
order by c1
fetch first 5 rows only;
C1
2
4
6
8
10
update t
set c2 = 0
where c1 = 9;
select c1 from t
where c2 = 0
order by c1
offset 5 rows
fetch next 5 rows only;
C1
10 <-- never see 9; 10 again!
12
14
16
18
select c1 from t
where c1 > 10
and c2 = 0
order by c1
fetch first 5 rows only;
C1
12 <-- never see 9
14
16
18
20
Whichever method you use, if people page back and forward in the results often for data that change regularly, customers will see unexpected results. But the seek method is generally "less wrong" and faster.
Exactly how you manage this depends on your requirements. Solutions include:
- Pre-fetching a large number of pages (e.g. 20 pages) and caching these at the application tier. When a user hits next, just get the next N from the cache. This works on the principle that people rarely go past page 2-3. This gives stable results, but the query for page one will be slightly slower
- Use flashback query to get results consistent to the time of the first page load.