Re: Diff. ANALYZE/ANALYZE FOR ALL COLUMNS ??

From: Chuck <skilover_nospam_at_softhome.net>
Date: Fri, 28 Jan 2005 14:12:16 -0500
Message-ID: <35vh4gF4r1mffU1@individual.net>

Spendius wrote:
> I've found noticeable differences between the different
> options of the ANALYZE TABLE command (in 8i):
>

>>analyze table sc.xxx estimate statistics;

>
> populates DBA_HISTOGRAMS and DBA_TABLES.LAST_ANALYZED, but
>
>

>>analyze table sc.xxx estimate statistics for all columns;

>
> also populates DBA_HISTOGRAMS with *much more* records but
> DBA_TABLES.LAST_ANALYZED remains to a null value...
>
> Could someone explain me all the differences according to
> the different options (FOR ALL INDEXES, FOR ALL INDEXED
> COLUMNS...) and in particular the difference between the
> first two commands quoted above, that feed DBA_HISTOGRAMS
> so differently ?
>
> Thanks a lot.
> Spendius

"analyze table ..."
Analyzes the table and all indexes.

"analyze table ... for all columns"
Creates histograms for the all columns of the table but does *NOT* analyze the table or indexes. BTW I was never sure why anyone would want to do this as the histograms will only make a difference on indexed columns. There's probably some feature of oracle that uses these stats but I don't know what it is.

"analyze table ... for table for all indexes for all indexed columns" Will analyze the table, the indexes, and created histograms for just the indexed columns. These histograms help the optimizer decide whether to use a table scan or index scan when including those columns in the WHERE clause. They really only need to be created for columns with highly skewed values though (like if one value occurs in 90% of the rows).

-- 
To reply by email remove "_nospam"

Received on Fri Jan 28 2005 - 13:12:16 CST