12 Posts on 12c: Row-Limiting Clause w/ Effective Dated Rows

I have the extreme pleasure to work with several PeopleSoft applications. Keen readers of this blog series may have noticed this already; plus 10 Internet points if you did. PeopleSoft makes create use of effective dated rows. (note to self, write a blog post on managing effective dated rows some day). 

When querying a table with effective dates, we usually end up with a query like so: 

 In this case, we are selecting the most recent row according to the maximum effective date and the maximum effective sequence. One might expect that the explain plan for this could be terrible, but it's actually not bad. We indexed properly.

Actually, this is a pretty good explain plan, so good in-fact that I wasn't sure how Oracle would improve on this, but I wouldn't have got this far if there wasn't some good pay off at the end.

We can re-write this SQL to use the row-limiting clause, and order by to get the same result, like so:

At the very least, this looks cleaner and it easier to read. But the explain plan is pretty amazing: 

Cost goes down, bytes returned are reduced, and we have an accurate data set returned. This is a pretty simple example, but more complete procedures which require effective dated rows would benefit from this type of re-write. 

NOTE: this is #11 in a series of 12. Tomorrow, will be the final installment.