Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Can statistics be copied between tables

Posted on 2009-05-20
5
Medium Priority
?
1,792 Views
Last Modified: 2013-12-18
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.
   
0
Comment
Question by:Milleniumaire
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24434675
Yes, check out the DBMS_STATS package.
 
 
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 1200 total points
ID: 24434709

1) Export MYTAB stats.
2) Do your thing (Create, Delete, Rename, ...)
3) Import MYTAB stats
 
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 300 total points
ID: 24435372
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');
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24435387
Sorry MikeOM_DBA, I didnt read carefully your post!
Excuse me!
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24435984
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.
 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question