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@xxxxxxxx> 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@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 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
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
