Re: Partition Statistics

Hi Chris

>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).

I completely agree. Though in some cases (large tables) this may be unpractical and even not necessary. See below.

> (please don't mix the concept of global statistics with
>the statistics at table level... here I'm speaking from global
>statistics).

Thanks for the correction. I would say this is exactly the point. If the global statistics are very close the table statistics of a partitioned table then the gathering of table statistics may be done less frequently or completely omitted. Of course the question remains how to verify that they are close. Possible solution is to gather the table statistics from time to time (into a predefined table) and compare them with the computed global statistics on table level. This approach may bring significant saving of resources compared with gathering table statistics on each add/drop partition.

If I go back to my original post, I'd say the listed conditions for rolling window partitioning may be thought as a hint whether this approach is suitable or not.

Of course in case of very "heterogeneous" partitions the table statistics should be gathered.

>>I observed relatively precise CBO estimation on queries of a partitioned
table with local statistics only.
>For queries with or without partition pruning?

With. You are right again.

regards

J.Nemec

http://www.db-nemec.com

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