Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: March 30, 2020 - 3:42 am UTC

Answered by: Chris Saxon - Last updated: March 30, 2020 - 2:32 pm UTC

Category: SQL - Version: 18.6

Viewed 100+ 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 we 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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.