Re: update stats on partitioned tables

From: Randolf Geist <>
Date: Fri, 03 Apr 2009 09:17:25 +0200
Message-Id: <>

> I would recommend:
> - gather partition stats (granularity=>'partition')
> - gather table stats (granularity=>'global')

> Gather partition stats for the loaded partitions, then gather global
> stats. This will save the time of gathering stats on data that has
> not changed.

> Starting with you can consider using DBMS_STATS.COPY_TABLE_STATS to
> copy stats from a previous partition to the new partition. This would ensure
> your execution plans are the same across all partitions.

Another option on top of the two mentioned above is the newly introduced "APPROX_GLOBAL AND PARTITION" option in It's a kind of backport simulation of the new incremental statistics feature introduced in 11g.

The optimizer group has published a blog note about maintaining statistics on large <a href="">partitioned tables</a> which covers these new options.

Note that both copy_table_stats and APPROX_GLOBAL AND PARTITION seem to require one-off patches to be installed to work properly, as mentioned in the blog post. See also Metalink Note 7116357.8.


Oracle related stuff blog:

SQLTools++ for Oracle (Open source Oracle GUI for Windows):

Psssst! Schon vom neuen WEB.DE MultiMessenger gehört? Der kann`s mit allen:
Received on Fri Apr 03 2009 - 02:17:25 CDT

Original text of this message