SQL for pagination queries - memory and performance

How to best write your SQL when doing pagination style queries against the database. In this video you'll learn:

- the syntax options you must use to ensure the correct results
- why doing it correctly gives you FASTER results
- why doing it wrong could destroy your server

Subscribe for new tech videos every week

My current favorite Oracle stuff:

- Favorite 12.2 feature: Almost all structural maintenance can be done online. No more outages.

- Favorite 18c feature: Killing a runaway SQL without killing the entire session.

- Favorite 19c feature: Handling JSON just got a lot easier!

Music: Night Owl (Broke For Free)

#pagination #sql #performance

[Be_Thinking]

pagination is not easy but you make it easy ty

[DoubleM]

I'm confused by the first query example at: 0:09 - Is it a valid SQL query when you have "LIMIT" before "ORDER BY" clause in oracle?
I'm asking because in postgres it's invalid (I had to check, lol).

In postgres you're forced to do order by, then limit, so first example would be:

select id, name, hiredate
from employees
order by hiredate desc
limit 5;

And it would work as expected. No need to inline view or sub-queries.

[MandeepSingh-eub]

Umm I'm confused because they say the order by clause is executed first and then the limit clause. So, shouldn't the first example work just fine? Or is it that its the order of the limit and where clauses that needs to be changed?

[berndeckenfels]

Hm, why is it stopkey in one and pushed rank in the other? Which one will be used when you set the (oci/Jdbc) result count?

[RayZde]

You also need the total rows for a pagination query.

[mishasawangwan]

hey thanks for uploading this — do you have the entire talk uploaded somewhere?

[jrs_devs]

I suppose this (0:21) is correct with MS SQL Server, isn't it?. Because "ORDER BY" is not valid when used within views.