Re: Need opinions on "Compute Statistics"

ANALYZE TABLE<table> COMPUTE STATISTICS is the start of the command. It is folowed by

- FOR TABLE : only stat are computed for the table
- FOR ALL INDEXES : only stats are computed for all the indexes on the table
- FOR ALL COLUMNS : for each column of the table
- FOR ALL INDEXED COLUMNS : for each indexed column of the table
- FOR COLUMNS <column1,column2,...> : for the columns specified in the list
- or any combination of the above.

For columns, you can specify a SIZE which specifies the maximum number of buckets for the column histogram (default is 75, range from 1 to 254)

(read your SQL Reference, it's all in there...)

I know that Oracle recommends to use the DBMS_STATS package. But when I follow such a session, I see (v$sqlarea) that behind the scenes, in some cases, Oracle uses the ANALYZE command.

In my opinion, not ALL columns need histograms, for example quantities (NUMBER), some/most of the date columns, columns that are NEVER used in a WHERE clause. A SIZE 0 can not be used. How to acomplish this? With DBMS_STATS, the default of method_opt is 'FOR ALL COLUMNS SIZE 1'...

"Luc Gyselinck" <luc.gyselinck_at_pandora.be>

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:b5hsdv0kqsbgt1i8eac1lk4h1q5d6md72p_at_4ax.com...
> On Wed, 04 Jun 2003 19:42:43 +0200, Sybrand Bakker
> <gooiditweg_at_sybrandb.demon.nl> wrote:
>
> ...
> >>1. If I use a statement like "ANALYZE TABLE A_PRODUCT_DETAILS COMPUTE
> >>STATISTICS" is this computing the statistics on the indexes also?
> >NO. You need the cascade option
> ...
>
> Sybrand, you are confusing COMPUTE STATISTICS (which computes the
> statistics on the indexes also) with VALIDATE STRUCTURE.
>
> Jaap.
Received on Wed Jun 04 2003 - 16:11:48 CDT