Notes 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:Aajac.139090$Cb.1489695_at_attbi_s51...
> > From metalink:
> >
> > "Please note that the decision to unnest a subquery is not costed in
> > Oracle9i. The decision to unnest a subquery is taken based on a set of
> > heuristics (rules) before the query is optimized."
>
> Whatever the 'set of heuristics' Oracle implements in 9i is, it sure
creates
> some atrocious execution plans.
I think the heuristics may be little more than:
if the result set is the same, do it.
There was an enhancement in 9.0 that added
code to recognise increasing numbers of
options - (I thought it was talking cost, but in
fact I now realise that it must have been talking
about patterns).
> We had to add an ugly hack (WHERE ROWNUM
>
> 0) to quite a few of our queries in order to prevent Oracle from
un-nesting
> them ( the hint does not always work).
I did find a note that said you should put the
unnest hint in the main query - I believe it is
wrong (though I now hesitate about making a
firm claim, given my last error on unnesting).
Whenever I've wanted to unnest, or no_unnest,
I've always put the hint in the subquery that I
want to fix, and it's never failed me yet.
>
> Rgds.
>
>
Received on Tue Mar 30 2004 - 14:15:24 CST
