RE: Incorrect cardinality estimate

From: Joseph Amalraj <joseph_at_amalrajinc.com>
Date: Thu, 1 Feb 2007 07:53:11 -0800 (PST)
Message-ID: <136895.29185.qm@web603.biz.mail.mud.yahoo.com>

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

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




--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 01 2007 - 09:53:11 CST