"Syntactic sugar" - I like that. It perfectly represents the way Oracle
converts the new "simple" row limiting code. I did a bunch of work at a client
about this recently, and my recommendation to Development was to keep using the
old methods. Whilst it's great that Oracle have added this syntactic
functionality to finally do the same as the "top *" syntax in SQL Server, using
an analytic windowing function to implement it can significantly change the
execution plans.
For now at least, I'm sticking with an ordered in-line view, and a rownum
restriction on the outer select (well, for any select involving joins).
Neil.
Subject: Re: 12c row limiting clause woes
From: woodwardinformatics@xxxxxxxxxxxxxxxx
Date: Mon, 5 Sep 2016 10:42:10 +0100
CC: oracle-l@xxxxxxxxxxxxx
To: xt.and.r@xxxxxxxxx
Thanks Sayan. I experimented a bit with the test code making 'a' the PK, and
could see the underlying PK index use, but was still exceptionally surprised
with the query cost and row estimates. I hadn't considered that row limiting
in this sense was just some sort of 'syntactic sugar' (your words) for some
internal transpiler to rewrite the code using an analytic function. Looking
at the plan however, it seems quick obvious now, especially the filter
ROW_NUMBER() OVER ..... In my code, this is a 12c new feature that
performance implications will mandate I just do not use.
Thanks
Mike
