Re: SQL tuning

My last message was not complete ...

Any idea, hints or events ..., to solve this problem ?

Thanks

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c72h2j$13m$1_at_titan.btinternet.com>...
> This looks like a bug in the optimizer calculation
> for the FILTER operation.
>
> 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
>
> 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
>
> "dias" <ydias_at_hotmail.com> wrote in message
> news:55a68b47.0405011704.3ba734c7_at_posting.google.com...
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=3)
> > 1 0 SORT (AGGREGATE)
> > 2 1 FILTER
> > 3 2 INDEX (FAST FULL SCAN) OF 'IT1' (NON-UNIQUE) (Cost=7
> > Card=1252 Bytes=3756)
> > 4 2 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=6)
> > 5 4 INDEX (RANGE SCAN) OF 'IT2' (NON-UNIQUE) (Cost=1
> > Card=1 Bytes=3)
> > 6 4 BUFFER (SORT) (Cost=1 Card=1 Bytes=3)
> > 7 6 INDEX (RANGE SCAN) OF 'IT3' (NON-UNIQUE) (Cost=1
> > Card=1 Bytes=3)
> >
>
> Note that FILTER is effectively a nested loop in this plan.
>
> Line 3 estimates a cost of 7 to return 1,252 rows.
>
> Line 4 should operate once per row returned by line 3,
> at a cost of 2 per operation.
>
> The total cost of the operation should therefore be:
> 7 + 1252 * 2 = 2511
> Instead it is
> 7 + 1 * 2 = 9
>
>
> If you put in an UNNEST hint on the subquery, you
> may find that the hash semi join that appears is just
> a little more expensive than the calculated cost of
> the FILTER.
>
>
> 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.
Received on Sun May 02 2004 - 13:22:48 CDT