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

"Chuck" <skilover_nospam_at_softhome.net> wrote in message news:35vh4gF4r1mffU1_at_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.
>

Hi Chuck,

The probable selectivity of a return row set is important to the CBO when deciding join order of tables, method of joins, sort sizes, etc.. Histograms on such columns, even if not indexed, could be beneficial.

Cheers

Richard. Received on Fri Jan 28 2005 - 16:51:43 CST