Re: More on dbms_stats

On 08/10/2005 05:48:23 PM, Mladen Gogala wrote:
> Wolfgang Breitling wrote:
>
> > It potentially, and likely, creates an awful lot of histograms exactly
> > BECAUSE the method_opt setting of 'for all columns size 1' is ignored
> > and replaced with 'for all columns size auto'.
> >
> I prefer skewed columns, that is the columns from SYS.COL_USAGE$.

As was gently and privately pointed out, I badly mixed things up yet again. The table SYS.COL_USAGE$ is used by 'FOR ALL COLUMNS SIZE AUTO' method while FOR ALL COLUMNS SIZE SKEWONLY computes histograms for all columns. I tried accusing the manuals, but they did a good job explaining that:

• integer : Number of histogram buckets. Must be in the range [1,254].
• REPEAT : Collects histograms only on the columns that already have histograms.
• AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
• SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

I must be growing old.

-- 
Mladen Gogala
http://www.mgogala.com


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