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

Note in-line

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005

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

    The comment about only making a difference on indexed     columns is wrong, although commonly believed to be true.

    Any column appearing in a WHERE clause may be a     suitable candidate for a histogram. It doesn't have to be     in an index. Oracle did a great dis-service to the user     community by introducing the 'for all indexed columns'     option. It gives the wrong idea, and is often used to generate     redundant histograms.

> 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 Sat Jan 29 2005 - 04:21:31 CST