RE: Incorrect cardinality estimate

  • From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 25 Jan 2007 16:35:15 -0500

I agree--CBO estimated cost was just slightly less for the desired path.  As 
Wolfgang pointed out, the 22-hour run gathered index stats via COMPUTE, so they 
differed from the original index stats, which were gathered via sampling.

Paul Baumgartel
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
Phone 212.538.1143

-----Original Message-----
From: Alberto Dell'Era [mailto:alberto.dellera@xxxxxxxxx]
Sent: Thursday, January 25, 2007 4:26 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; Wolfgang Breitling
Subject: Re: Incorrect cardinality estimate

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).
> 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"

Please access the attached hyperlink for an important electronic communications 


Other related posts: