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 Sun May 02 2004 - 16:29:34 CDT
