Re: Using bind variables for multiple values

From: Jonathan Lewis <>
Date: Mon, 24 Nov 2008 16:26:13 -0000
Message-ID: <>

"Robert Klemme" <> wrote in message
> 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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Mon Nov 24 2008 - 10:26:13 CST

Original text of this message