Can statistics be copied between tables

Is it possible to copy statistics between tables, specifically large partitioned tables?

We are using Oracle 10g ( 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.
LVL 16
Who is Participating?

1) Export MYTAB stats.
2) Do your thing (Create, Delete, Rename, ...)
3) Import MYTAB stats
Yes, check out the DBMS_STATS package.
Oracle Data Pump can export statistics only but there are some complaints:

  Importing statistics takes unreasonable amounts of time. We had to export and import one schema. Collecting statistics on this schema takes around 5 minutes. Importing the statistics using Data Pump took over 20 minutes. Oracles documentation fails to mention this little bit of information. I really hope this issue is fixed in 11g.

Work around: import with exclude=statistics flag and collect statistics later.

So a workaround is to  copy the statistics to a stattab using dbms_stats, export that table, import the table in the other database, and use dbms_stats to import the statistics in the other dictionary.

create stats table to hold exported stats data.
EXEC DBMS_STATS.create_stat_table('SCHEMA1','ST_TABLE');

export stats to stats table
EXEC DBMS_STATS.export_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');

transfer the stats table ST_TABLE to destinate location using method of choice, like exp/imp, create tabel as select ..., sqlplus copy etc.

import the stats into schema
EXEC DBMS_STATS.import_schema_stats('SCHEMA1','ST_TABLE',NULL,'SCHEMA1');
Sorry MikeOM_DBA, I didnt read carefully your post!
Excuse me!
No problemo, I use this a lot.
I compute statistics in the Reporting database, export to statstab. expdp/impdp stats table into production and import stats -- works like a charm.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.