Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

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

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.