RE: clarification on partitioned tables

Joe

>Right now all that is being used is the analyze command on 9iRel2 Solaris 8.

ANALYZE only gathers statistics of the segments (i.e. partitions or sub-partitions) and then "guesses" what the statistics at the logical level are (i.e. table or table/partitions). Such "guess" can not be good in all cases... Just think about the distinct key statistics. Partition P1 has 10 distinct keys, same for partitions P2. How many distinct keys has the table who belongs P1 and P2?

On the other side DBMS_STATS gathers statistics at all levels independently (except in 8i where in some situation DBMS_STATS calls ANALYZE). Oracle calls these statistics "global statistics" (e.g. see DBA_TABLES.GLOBAL_STATS).

For this reason, IMHO, only DBMS_STATS does a good job with partitioned tables.

>I want to move to DBMS_STATS to collect stats on a partitioned table ( and
>the schema ) in 10gRel2 on Solaris 10.

With DBMS_STATS you don't need to explicitly add GRANULARITY. The default configuration gathers them at partition and table level. In addition note that GATHER STALE is computed for each segment, not at table level. I.e. you could have a run that gathers the statistics of a single partition without updating the statistics at table level. IMHO this is usually a good thing, e.g. if a rolling window has been implement.

HTH
Chris

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