Re: Partition Statistics

Hi Steve,

>----- Original Message -----

>From: "Orr, Steve" <sorr_at_rightnow.com>

>To: <oracle-l_at_freelists.org>

>Sent: Wednesday, December 08, 2004 4:54 PM

>Subject: Partition Statistics

>
>After the above the value in the global_stats column is 'YES' for the
>specific partition recalc'd so now my question is what changed? Does the
>optimizer look at the dba_ind_partitions.global_stats column and does it
>make a difference

In my experience with rolling windows partitions you can expect satisfactory result while maintaining statistics on the partition level only.

Particularly when those conditions are fulfilled:

• the number of rows resp. avg_row_len in each partition is more or less the same
• the column statistics (above all density) is similar in all partitions for each column
• and most important - the global density is close to the local (partition) density for each column (This may be a common case when you have partitions containing daily or monthly data and a constant number of partitions)

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

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" .
I observed relatively precise CBO estimation on queries of a partitioned table with local statistics only.

In times of dynamic sampling there is always a danger of idle work if statistics are missing, so there is a possible compromise to refresh the global statistics not very frequently with appropriate sampling or even to set them manually. Once more this is valid only on rolling windows with the conditions above fulfilled.

> I created an index as follows

did you consider the possibility of computing statistics while creating index?

>What was it doing? Was it gathering "global" status by
>looking at all 700+ partitions even though I specified a single
>partition?

One of the advantages of dbms_stats over analyze table is that you can observe the SQL statement gathering statistics via standard querying of v$session and v$sqlarea.

(The second advantage is that you get reportedly different results:)

>Any good white papers on partitioning you would recommend?

Either do I. Any volunteers?

Regards

Jaromir D.B. Nemec

http://www.db-nemec.com

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