Re: Using bind variables for multiple values

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:6c7ef223-0934-4cf9-a44b-ad268937b86f_at_j32g2000yqn.googlegroups.com...
>
> I wondered: why is it that the join version does a full table scan but
> the IN ( subselect ) version does not? I did a 10053 trace and found
> out that the same access strategy which leads to the INDEX UNIQUE SCAN
> in the case of IN ( subselect ) is costed much higher (~ factor of 30)
> in the JOIN case. BASE STATISTICS are identical in both cases and it
> seems the culprit is the join cardinality (255 vs. 8168) which seems
> derived from the outer table cardinality. The big myth to me is: where
> does the CBO get the cardinality of 255 from all of a sudden since it
> originally assumed 8168?
>
>

If you check the cardinality, you will see that it is 255.25, which is EXACTLY 8168 / 32.

When Oracle doesn't know what the real statistics are, it uses some arbitrary estimate. In this case, because the IN approach transforms into an inline 'select distinct column_value' and Oracle doesn't know how many distinct values there are, I guess is simply assumes 32.

Just as 8168 is a side effect of the default block size, this 32 may also be based on the block size. So if you've got a database with a different block size handy, you might like to test what happens there.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html