12 Posts on 12c: Row-Limiting Clause

For several versions now, other database platforms, such as MySQL and Microsoft SQL Server have been able to select the first x number of rows; Oracle has not, until 12c. Over the next several days, and several posts, I'm going to discuss implementation and use cases based on this particular feature.

Before Oracle 12c, row limits had to be done like this: 

This works, mostly, but there were some challenges, such as plan management, data set unpredictability, and generally, it's just confusing to look at. I'm not afraid to say, this seems 'hack-ish'. Let's not even talk about pagination. 

Now with 12c, we can replace nested SELECT statements and rownum with this instead: 

Same basic premise, return only the first three rows, based on the ORDER BY sorting. Pretty simple actually. 

And if you want to page through the results? We can do that too: 

In this query, we skip the first row, and return the second row only. If you are thinking that you could replace the offset value and fetch first value with bind variable...you'd be totally right. Passing bind variables to page through a data set will make your optimizer very happy.

Let's look at the syntax and functional pieces: 

{OFFSET x ROW{S}} FETCH {FIRST | NEXT} y ROWS {ONLY | WITH TIES}

{OFFSET x ROW{S}}	(optional) Skip ahead to the xth row.
FETCH {FIRST | NEXT} y ROWS	Collect the FIRST or NEXT number of rows specified by the value y
{ONLY | WITH TIES}	based on sorting criteria, return either only y rows, or all rows containing the yth value.

For those of you counting, this is #9 in the series.