RE: Does Oracle have a Sense of Humour?
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
- Date: Wed, 7 Jun 2006 19:16:13 +0200 (CEST)
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)
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
