One of Oracle 12c's coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like: SELECT * FROM film ORDER BY film_id FETCH FIRST 1 ROW ONLY This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for … Continue reading Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering
Tag: pagination
10 SQL Articles Everyone Must Read
We've been blogging about Java and SQL for a while now, on the jOOQ blog. Over the years, while researching interesting blog topics, we've discovered a lot of SQL gems in the blogosphere that have inspired our work and our passion for SQL. Today, we're presenting to you a list of 10 articles that we … Continue reading 10 SQL Articles Everyone Must Read
Join the No OFFSET Movement!
Markus Winand from Use The Index, Luke! did it again. He started an exciting battle against one the biggest flaws in the SQL language: We've blogged about this before. OFFSET pagination is terribly slow, once you reach higher page numbers. Besides, chances are, that your database doesn't even implement it correctly, yet (and your emulation … Continue reading Join the No OFFSET Movement!
Faster SQL Pagination with Keysets, Continued
A while ago, I have blogged about how to perform keyset pagination (some also call this the "seek method"). Keyset pagination is a very powerful technique to perform constant-time pagination also on very large result sets, where "classic" OFFSET pagination will inevitably get slow on large page numbers. Keyset pagination is most useful for lazy … Continue reading Faster SQL Pagination with Keysets, Continued