Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Incorrect cardinality estimate

RE: Incorrect cardinality estimate

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 25 Jan 2007 14:35:58 -0500
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663BBFF@ENYC11P32005.corpny.csfb.com>


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). The operation took over 22 hours--this is a ~20M row table with 107 partitions. (Question: what is the effect of cascade=>true when creating a histogram for a single column?)

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 am now going to read Wolfgang's paper on interpreting 10053 trace files so that I can understand all of the content.

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Tuesday, January 23, 2007 2:04 PM
To: Baumgartel, Paul
Subject: RE: Incorrect cardinality estimate

At 11:38 AM 1/23/2007, you wrote:
>Wolfgang,
>
>Well, that's discouraging. So height-balanced histograms only
>address one side of value distribution skew--is that an inherent
>limitation in that non-popular values "get lost" in the histogram?
>
>Thanks for your help.

Yep, height-balanced histograms are of somewhat limited use. As far as I could determine so far they are only useful for popular values or for range predicates if there are (large) gaps in the domain values.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com



This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email


Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 25 2007 - 13:35:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US