Can statistics be copied between tables
Posted on 2009-05-20
Is it possible to copy statistics between tables, specifically large partitioned tables?
We are using Oracle 10g (10.2.0.4.0) which has much of the functionality included in the first release of 11g (as I've just found out).
We have a situation where large partitioned summary tables are rebuilt every night from source data using the following sequence of events:
create NEW_MYTAB table from source data
delete OLD_MYTAB table
rename MYTAB table to OLD_MYTAB table
rename NEW_MYTAB table to MYTAB
The result is that the newly created table is now called MYTAB, but it doesn't have any statistics on it. Currently it takes about an hour to gather stats (partition and global) on this table. I would like to copy the statistics from the OLD_MYTAB table to the MYTAB table as these will be accurate enough.
One thought is to export the OLD_MYTAB statistics into a stats table, update the "C1" column, which appears to be the table name (from OLD_MYTAB to MYTAB), and then import the statistics into the MYTAB table.
I was wondering if there was a better way of doing this.