Re: SQL tuning

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.