Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can statistics be copied between tables

Posted on 2009-05-20
5
Medium Priority
?
1,789 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

886 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