12C Paging through Data and Restricting Result Sets

Prior to Oracle 12C, if you wanted to page through result sets only returning a bit at a time, or only wanted the first dozen lines of data, it was a right pain. Writing your SQL, then having it as an in-line view with order by, and a rownum on the outer view. And the whole or that in an in-line view with another named rownum around that to create a sliding window. Horrid. Horrid. Horrid.

I have been covetous of the Top function within SQL Server SQL for years and years.

Well FINALLY! In 12C we have our very own Oracle function to rival that of the Top function in SQL Server. Something to make sliding cursors at least a bit more readable. Step forward FETCH FIRST n ROWS!

SELECT * FROM <table> WHERE … ORDER BY column FETCH FIRST 10 ROWS ONLY;

SELECT * FROM <table> WHERE … ORDER BY column OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

(note: this means the first record should be record 11 – one more than the offset)

It also has other flexibility: FETCH FIRST 20 PERCENT or FETCH FIRST 10 ROWS WITH TIES (i.e. where the values are the same), FETCH LAST 30 ROWS, etc

The numbers supplied can be bind variables, too. As we’d expect.

It’s worth noting that SQL will optimize differently to using the ROWNUM method of paging though a result set. That’s doesn’t mean better or worse, just differently. In some cases it will be better, and in other cases it may well be worse. It depends upon the table design and the SQL query.

Under the covers, Oracle is actually doing a query transformation, rewriting the query using a ROW_NUMBER() OVER (ORDER BY ) analytics function. This is the other way of paging through a result set, effectively doing:

SELECT * FROM
(SELECT column, ROW_NUMBER() OVER (ORDER BY column) R FROM table)
WHERE R BETWEEN 51 and 100;

I think you’ll agree that, if nothing else, the new 12C syntax is a lot easier to read.

For a much better post about how it works, you should swing by Tim Hall’s website. Damn it Tim, I found that half way through writing this (which is why it’s short). Stop being so good at blogging 🙂