RE: clarification on partitioned tables
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "Joe Smith" <joe_dba@xxxxxxxxxxx>
- Date: Fri, 16 Jun 2006 11:02:49 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
