I'm on Oracle 9i and use the following:

exec dbms_stats.gather_table_stats (ownname=>'schema_owner',tabname=>'table_name',
partname=>'partition_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'for all indexed columns', cascade=>TRUE);

However, I've noticed what I think is a bug in dbms_stats. When I perform the above
command on a table (250M rows, 120 indexes or so) it takes hours. Even when I
force the smallest possible sample size it takes something like 6 hours. I have
about 2M rows per partition, and about 150 partitions in the table. So as you can
see, if I want to fully gather stats on the table as is, it takes weeks (6 hours
by 150 partitions is 900 hours or about 37 days).

If, however, I create a table outside of the main partitioned table, create
all my local indexes on it and issue dbms_stats.gather_table_stats on this
external table, it takes no more than 30 minutes ... and then I can alter table
exchange partition the exterior table "into" the partitioned table in a few
seconds.

The questions is: why the huge time difference when gathering table stats
against a partition versus a singular table? Anyone else seen this?

thanks, todd

>
> I have a partitioned table in my schema. The cron job runs an analyze
> command to collect statistics. Does the analyze command collect stats on
> the table partitions? Do I have to explicitly use the:
>
>
> GRANULARITY => 'PARTITION'
>
> with dbms_stats to collect partition stats? I am reading Doc ID:
> Note:237538.1
>
> any clarification or enlightment is appreciated.
>
> thanks.
>
> _________________________________________________________________
> Express yourself instantly with MSN Messenger! Download today - it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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