Solved

Can statistics be copied between tables

Posted on 2009-05-20
5
1,782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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 100 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

623 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