Re: 12c row limiting clause woes

Mike,
The 12c 'fetch first rows' do not implicitely set optimizer to first rows optimization, so you have to add FIRST_ROWS() hint for correct optimization (cf. blog post
<http://blog.dbi-services.com/oracle-rownum-vs-rownumber-and-12c-fetch-first/> )
Mark, the behavior you describe (execute for all rows but fetch only first) happens with result cache (cf. another blog post <http://blog.dbi-services.com/result-cache-and-12c-fetch-first-n-rows/>) Regards,
Franck.
Franck Pachot | Senior Consultant & Oracle Technology Leader | Oracle Certified Master 12*c* and Oracle ACE Director

On Wed, Sep 7, 2016 at 12:52 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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
>
> rn > :offset and rn<= :offset + :nRows
>
>
>
> 2)
>
> 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
>
> -- 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