Moving partition statistics

Over in another place, Doug Burns has been writing on the joys of reproducing a full size database as test instance. He has even (graciously) put up with my wittering about things that not relevant to his articles. So as not to clutter his blog with a stream of off-(his)-topic posts here is a digression of my own, isolated.

One of things that concerns me greatly, that is a data warehouse designer, is query performance; I deal with monster reads and aggregations; rarely are my users interested in single fact table rows (but enough of them are to prevent me from ignoring those queries types). But the key thing is that I need the best query plans as even those a tad out when amplified by the sheer bulk of repetition waste resource and annoy users. Occasionally we run into CBO problems that need Oracle support to investigate and for this we need evidence and test cases (preferably without customer's business data) I have written about this a couple of times and Tom Kyte may have mentioned it a bit too.

One of the irks about a small (half sized) test system is that not all of the data is loaded, and more importantly not all of the partitions are present. This is fine for a lot of tests but some need production volumes or least us to convince the system that we have the full data set. Often we can influence things by using some of the DBMS_STATS methods to export table stats to a stats table, then using conventional techniques such as exp/imp move its content to our test system and then use the DBMS_STATS.import_table_stats process to convince the optimiser that we have the same volume and distribution of data. However life is not so simple for partitioned tables. Even though a lot of my queries eliminate partitions on a join key or access several partitions, that is they are going to only use GLOBAL table statistics (partition stats are not even going to be considered by the CBO) there seems no easy way to just transfer global stats. I can specify an individual partition of statistics to export, but at table level it will always include all of the available partition stats in the export. Which means my import fails as the partitions are not there to receive the partition stats (OK, I know that is an oversimplification of the reality)

I do have ways round this problem, but that is not the point...