FreeLists / oracle-l / RE: Incorrect cardinality estimate

[Joseph Amalraj <joseph@xxxxxxxxxxxxxx>]

That's very good advice for gathering statistics.

Please let me know how do you 

gather any column statistics separately with estimate_percent=>100 

Thanks

Joseph Amalraj

Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote: At 12:35 PM 1/25/2007, 
Baumgartel, Paul 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).  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?)

It also analyzes all indexes, in addition to building the histogram. 
In other words, you don't want to do that.
I always
- gather table stats separately with cascade=>false, method_opt=>'for 
all columns size 1' and, if necessary because of table size, use 
estimate_percent=>dbms_stats.auto_sample_size
- gather index stats separately with estimate_percent=>100
- gather any column statistics separately with estimate_percent=>100 
- and of course cascade=>false
- do any other statistics manipulation

in that order



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 
______________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l