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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sayan Malakshinov
Sent: Monday, September 05, 2016 6:27 AM
To: neil_chandler@xxxxxxxxxxx
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@xxxxxxxxxxx>
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@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
