Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Incorrect cardinality estimate
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_at_centrexcc.com> 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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 01 2007 - 09:53:11 CST
![]() |
![]() |