Re: update stats on partitioned tables
Date: Fri, 03 Apr 2009 09:17:25 +0200
> 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 10.2.0.4 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 10.2.0.4 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="http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html">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:
Psssst! Schon vom neuen WEB.DE MultiMessenger gehört? Der kann`s mit allen: http://www.produkte.web.de/messenger/?did=3123 Received on Fri Apr 03 2009 - 02:17:25 CDT