Oracle query results pagination without TABLE FULL SCAN data access method

Question

There are plenty of questions on stackoverflow on how to perform pagination correctly, for oracle the most popular answer is something like this:

select * from (
    select row_.*, rownum rownum_ 
    from (select * from some_table) row_ 
    where rownum <= N) 
where rownum_ > M;

I've seen this query everywhere and Hibernate also generates it for pagination. It's probably ok for most cases but it requires full table scan and slows down significantly on huge amounts of data.

There is a hint that's supposed to help selecting first N rows

/*+ FIRST_ROWS(5000) */

but it doesn't help in case of selecting second page and seems to use full scan also, at least that's what "explain plan" says to me.

To deal with this problem I'm currently implementing custom pagination - reading ids of all rows in a table and splitting them on ranges so that pagination query can look something like this:

select * from some_table where id between N and M;

I was hoping to find a vendor solution to this problem but haven't succeeded so far.

So, the question is - am I reinventing the wheel or there is realy no way to implement pagination on oracle without full scan?

Upd: in Oracle 12c they introduced a new syntax for paging:

OFFSET N ROWS FETCH NEXT M ROWS ONLY;

I've tried explain plan on this and it seems to be just an alias for

select * from (
    select row_.*, rownum rownum_ 
    from (select * from some_table) row_ 
    where rownum <= N) 
where rownum_ > M;

UPD2: just found a similar question - Oracle and Pagination looks like I've been inattentive while searching for duplicates before. So, most probably the answer on my question is negative but still, maybe something has changed since then...

Answer 1

First of all: A full table scan is not always the devil.

1. Check at your explain plan the costs of your query without pagination
2. Check it inside your pagination solution

Also when you do tests, try to use large tables with high values of pagination

Additional points:

• Pagination without ordering is always dangerous because you cant be sure, in which order Oracle provide your result for your next "page" -> possible not reproducible results
• Also in an ordered result is it possible, that a "new entry" in a page before your current "view" affects your "next page"

I except, that you like to "store" a query in DB and get the data, page by page, until something changed in the underlined data?