RE: 12c row limiting clause woes

I must have missed a memo. I actually thought the new return limit syntax was *intended* to do the full execution and limit only the returned results for conveniently returning a small sample of the full output without changing the plan and underlying calculations versus returning the whole set, not as a substitute for kicking in a “stop-key.”  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov Sent: Monday, September 05, 2016 6:27 AM To: neil_chandler_at_hotmail.com
Cc: Oracle-l
Subject: Re: 12c row limiting clause woes  

Mike,  

I'd prefer to use row limiting clause for very simple queries only, like:

select a from foo

where :a > :x

order by a

--offset 5 rows

fetch next 5 rows only --with ties  

Notice that I've commented out "with ties" and "offset": even these simple additions break cardinality estimation.  

Just don't forget that if you use bind variables(instead of hardcoded 5/5) you'll get same cardinality mistakes using analytic functions with the same functionality.

Compare:

1)

select a

from (

      select a, row_number()over(order by a) rn

      from foo

      order by a asc

)

where

• rn > 5 and rn <= 5 + 5

where

 rn > 5 and rn <= 5 + 5

• rn > :offset and rn<= :offset + :nRows

On Mon, Sep 5, 2016 at 12:52 PM, Neil Chandler <neil_chandler_at_hotmail.com> wrote:

"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
 

-- 

Best regards,
Sayan Malakshinov

Oracle performance tuning engineer

Oracle ACE Associate
http://orasql.org



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