Showing Oracle query results without TABLE FULL SCAN data access method

Question

There are many stackoverflow questions about how to paginate 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 have seen this query everywhere, and Hibernate also generates it for pagination. This is probably normal for most cases, but it requires a full table scan and slows down significantly on huge amounts of data.

There is a hint that should help select the first N lines

 /*+ FIRST_ROWS(5000) */ 

but it doesn’t help if you select the second page and it seems to also use a full scan, at least what tells me the “explanation plan”.

To deal with this problem, I am currently implementing a custom pagination - reading the identifiers of all the rows in the table and dividing them into ranges so that the pagination request looks something like this:

 select * from some_table where id between N and M; 

I was hoping to find a solution to solve this problem, but so far this has not been possible.

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

Update: In Oracle 12c, they introduced a new swap syntax:

 OFFSET N ROWS FETCH NEXT M ROWS ONLY; 

I tried to explain the plan for 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: Found a similar question - Oracle and Pagination look like I was inattentive when looking for duplicates before. So, most likely, the answer to my question is negative, but still, perhaps, something has changed since then ...

Answer 1

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

• Check your plan for the cost of your request without pagination.
• Check it out in your pagination solution.

Also, when you do tests, try using large tables with large pagination values

Additional items:

• Separating pages without an order is always dangerous because you cannot be sure in what order Oracle will provide your result for your next “page” → possible irreproducible results.
• Also, in an orderly result, it is possible that the “new record” on the page before your current “view” affects your “next page”

I except that you like to “store” the query in the database and receive data by page until something has changed in the underlined data?