Re: 12c row limiting clause woes

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Mon, 5 Sep 2016 13:27:06 +0300
Message-ID: <CAOVevU7X65p=LPoRxay5KgPb+ZzaXkdzBSjDEO7-HLyeAkS_jg_at_mail.gmail.com>

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

Received on Mon Sep 05 2016 - 12:27:06 CEST