RE: Incorrect cardinality estimate
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Thu, 25 Jan 2007 13:24:19 -0700
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
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
