Re: Incorrect cardinality estimate

Comments embedded.

On 1/25/07, Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com> wrote:
> I'm enjoying the discussion this has prompted.
>
> Just for fun, I re-created the column histogram, this time adding cascade=>true (hadn't done that before).
(snip)
> For the column, density is .006292113.
>
> Now, wonder of wonders, I am getting the (different) optimizer plans I want for non-popular
> (492 rows) and popular (2143642 rows) values of ODS_PROCESS_DATE. 10053 trace
> shows that, for non-popular value, optimizer estimates cardinality to be 1223485, but
> calculates cost of single-index lookup (desired path) to be just slightly less than the cost
> of the index join. For the popular value, cardinality is computed to be 2296622, and the
> index hash join in chosen.

I think you're simply getting lucky - the cardinality estimation has not changed,
but with the proper index statistics in place, the hugely wrong 1223485 instead of
the correct 492 is still low enough to favor the index access.

Obviously - a small change in the data distribution may easily increase the wrong 1223485 estimation over the "index is better" threshold ;)

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l