Skip to Main Content
  • Questions
  • Comparing pagination methods - offset vs seek

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: April 09, 2020 - 3:31 pm UTC

Last updated: September 16, 2021 - 3:28 pm UTC

Version: 18.6

Viewed 1000+ times

You Asked

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,

and we said...

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.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

Geraldo, April 21, 2020 - 12:43 pm UTC

Thanks for the help. It really helped.

Follow up

A reader, September 15, 2021 - 9:22 am UTC

What about if it is required to order by any_date_value DESC but c1 is db auto generated? The last c1 value will not necessarily be the last value shown to clients.

Regards.
Chris Saxon
September 15, 2021 - 2:51 pm UTC

The point is you keep track of whatever the last value of C1 that was shown to the client.

But you do raise a good point that sorting by sequence values may not match the sort for date values. You should use whichever column(s) you're sorting on to determine the starting point for the next page. Though you may also need to add the primary key to the seek in cases when you're ordering by non-unique values.

Follow UP

A reader, September 16, 2021 - 1:03 pm UTC

So, in this case the seek column should be the column_date instead of the pk.

Right?


Chris Saxon
September 16, 2021 - 3:28 pm UTC

In principle - though you need to be careful with dates. If you're using a date (or timestamp(0)), it has second granularity, meaning there's a high chance of many rows having the same date.

In which case you should add the PK to the sort (and seek) to ensure the results are deterministic.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.