RE: 12c row limiting clause woes

"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_at_strychnine.co.uk
> Date: Mon, 5 Sep 2016 10:42:10 +0100
> CC: oracle-l_at_freelists.org
> To: xt.and.r_at_gmail.com
>
> 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
                                               

--
http://www.freelists.org/webpage/oracle-l