RE: Does Oracle have a Sense of Humour?

Hello Laimutis,

>>It's not clear why Oracle chooses t_idx2 index full scan versus range
>> scan on t_idx when index explicitly not specified.
>
> This is the darned covering index effect.
> Oracle assumes it's better to scan t_idx2 which contains column x.
>

Exactly, as the cost of the IN list index range scan is linearly growing with the number of members of the IN list, there is some threshold where the cost is higher than the constant cost of the index full scan of the second index. As the data is prepared so that the cost of a single index range scan is relatively high, this effect is achieved with a small IN list. I have elaborated this more in detail in the paper to this phenomenon (http://www.db-nemec.com/SenseofHumour.html)

> One more thing:
>
> it could be(also should not) that this query hits the bug 3663924 "Bad
> cardinality for out-of-range range predicates", which is available at 9i
> and 10g. It looks very much like that. To be on the safe side the
> estimate_percent must be 100% (it is 15% in this case)
>

I don’t thing this behaviour can be described as a bug as all formulae from Johathan's Book seems to be valid. The behaviour is also independent of the sample size of statistics (the switch may be triggered with a somehow larger IN list while using smaller sample size). Above all (IMO) the cost of a IN list predicate differs from the calculation of range predicate (they are calculated as the cardinality of the list times the cost of equation predicate; you may correct me if this is not precise inough), so the bug on range predicate shouldn't be relevant here.

Regards,

Jaromir
>
> Brgds, Laimis N.
> (btw, linkejimai)
>

--
http://www.freelists.org/webpage/oracle-l