Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
"VC" <boston103_at_hotmail.com> wrote in message
news:Gwmac.140439$Cb.1493365_at_attbi_s51...
>
> I should have been more precise in my wording. The no_unnest hint does
sort
> of work if placed in the subquery but the resulting plan, for more-or-less
> complexx queries, is not as good as with the rownum>0 trick The rownum
> trick creates a plan identical to what we had under 8i.
>
> In case you wanted to see the query, I've sent it to you via e-mail.
>
I've sent a note off-line about the query and plan,
but thought I'd mention here that your query included
a UNION, and Metalink note 144967.1 (which was
last revised about 4 weeks ago) includes this comment:
<<quote>>
A specific exmaple is where we have 2 tuned queries containing subqueries
which are then joined by a union. Because the union forces the data to be
sorted to remove duplicates, the query is converted to a UNION ALL and a
DISTINCT keyword is added. These changes mean that the resultant view is now
non-mergeable allowing semi or anti joins to be contemplated. If these are
chosen then there may be performance differences compared with the
un-transformed query.
<<end quote>>
It almost looks as if it were written to address your problem,
although in your case, the default plan ended up with one unnest
and one semi-join.
Received on Wed Mar 31 2004 - 01:12:41 CST
