Re: SQL tuning

Hi Jonathan,

The /*+ unnest */ hint fixed the problem. I thought that when the parameter "_unnest_subquery" is set to true (by default) it was sufficient, but ...

Thanks again

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c73p7u$c2i$1_at_hercules.btinternet.com>...
> Further comment 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
>
> June 2004 UK - Optimising Oracle Seminar
> July 2004 USA West Coast, Optimising Oracle Seminar
> August 2004 Charlotte NC, Optimising Oracle Seminar
> September 2004 USA East Coast, Optimising Oracle Seminar
> September2004 UK - Optimising Oracle Seminar
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:...
> >
> > This looks like a bug in the optimizer calculation
> > for the FILTER operation.
>
>
> > The really odd thing about this is that the manuals
> > say that Oracle does not cost UNNEST in 9i, it
> > just does it - but clearly, this example has used cost
> > to decide.
> >
>
>
> I've spent a little more time looking at this. As you saw
> with your 10053, the cost calculation without the index
> are:
> Stage 1: subquery cost
> Stage 2: join T1 to VW_SQ_1
>
> With the query you get:
> Stage 1: subquery cost - handled incorrectly
> Stage 2: simple select on T1
> Final cost = stage 1 + stage 2
>
> The indexed stage 1 gives a computed cardinality
> of 1 on T2 and T3, which implies an assumption that
> there is an outer query forwarding an object_id value,
> which is why I made the comment 'handled incorrectly'.
>
>
>
> In my previous post, I made the assumption that
> Oracle was making a cost-based decision about
> the unnest - it isn't, I think it's simply failing to look
> at an unnest at all.
>
>
> As a quick fix, you can put the /*+ unnest */ hint
> into the subquery. That fixed it for me.
>
> In passing, 10g gets it right - using index fast full
> scans on the indexes instead of tablescans, and
> does the hash join for the subquery, then has
> semi-join for the whole thing.
Received on Mon May 03 2004 - 02:50:42 CDT