Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition Statistics

Re: Partition Statistics

From: jaromir nemec <>
Date: Wed, 8 Dec 2004 20:33:47 +0100
Message-ID: <016001c4dd5c$d84b26b0$3c02a8c0@JARAWIN>

Hi Steve,

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

>From: "Orr, Steve" <>

>To: <>

>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:

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

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?


Jaromir D.B. Nemec

Received on Wed Dec 08 2004 - 13:42:18 CST

Original text of this message