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 woesand could see the underlying PK index use, but was still exceptionally
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,
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
