Thanks for the question, Geraldo.
Asked: March 30, 2020 - 3:42 am UTC
Last updated: March 30, 2020 - 2:32 pm UTC
Version: 18.6
Viewed 1000+ times
You Asked
Bello, Ask Tom Team.
I need to build a report that connects to a 18c Oracle database.
There are big tables and I need to paginate the results. I want to show 50 rows per page in the report.
I have read about Fetch, Offset features since Oracle 12. I also have these features are slower than the old rownum. Is it right? Which one do you recommend?
2. How can I get 50 rows pagination with the recommended method above?
Thanks in advanced.
and Chris said...
There was a bug where possible indexes for fetch first were costed incorrectly. This is fixed and has patches going back to 12.1.0.2:
https://blogs.oracle.com/optimizer/fetch-first-rows-just-got-faster 2. You get the first fifty rows with:
select * from t
order by ...
fetch first 50 rows only;
And the next fifty with:
select * from t
order by ...
offset 51 rows
fetch next 50 rows only;
Note that pagination in general has issues when the data changes between page loads. This can lead to unexpected results when using offset. Read more about this at:
https://use-the-index-luke.com/no-offset