RE: Partition Statistics

Hi Jaromir

>In case when all indices on the partitioned table are defined local =
there is
>really not very much "value added" in the global statistics. (Of course =
in
>case when e.g. the local density of some columns is low but the global =
is
>high this is not true).

In my opinion statistics at table level should be gathered. In fact I =
see no problem doing it and this can only be beneficial for the =
optimizer (e.g. when no partition pruning is used).

>Mathematically seen there is no possibility to calculate all global
>statistics from local (partition) statistics - particularly the =
density. But
>if the above condition are in place, it isn't a big task. I assume =
there is
>an algorithm for default global statistics for partitioned table in =
case
>that local statistics are defined. I assume this could be something =
like
>"get statistics from one (or more) partition(s) and do a little  math =
on it"

As you wrote it's not possible to compute table statistics starting from =
partition statistics. Therefore Oracle has introduced global statistics =
in DBMS_STATS (please don't mix the concept of global statistics with =
the statistics at table level... here I'm speaking from global =
statistics).

>I observed relatively precise CBO estimation on queries of a =
partitioned
>table with local statistics only.

For queries with or without partition pruning?


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

Other related posts: