Re: Incorrect cardinality estimate
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: paul.baumgartel@xxxxxxxxxxxxxxxxx
- Date: Thu, 25 Jan 2007 22:26:15 +0100
Comments embedded.
On 1/25/07, Baumgartel, Paul <paul.baumgartel@xxxxxxxxxxxxxxxxx> 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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
