RE: Incorrect cardinality estimate

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 25 Jan 2007 13:24:19 -0700
Message-Id: <20070125202319.B6CED5BB6E5@turing.freelists.org>

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

Received on Thu Jan 25 2007 - 14:24:19 CST