Home » RDBMS Server » Performance Tuning » Copying statistics of one table to another
Copying statistics of one table to another [message #200261] Mon, 30 October 2006 02:34 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have 2 tables with the same structures, same number of rows, same data - except for one column which is neither indexed nor used in any referential integrity. Example, TableA and TableB:

TableA has columns col1 and col2. TableB is created as:

create TableB as
select col1, (col2 + 1) col2 from Table A;

TableB has same indexes as TableA except for the name. TableA has index1_A, index2_A.. and TableB has index1_B, index2_B..

In short, the tables are exactly in structure and data. Is there a way I can just copy the statistics of TableA to TableB?? It will take me 5 hours to analyze TableB if I can't.

Thanks.
Re: Copying statistics of one table to another [message #200268 is a reply to message #200261] Mon, 30 October 2006 03:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10752/stats.htm#41001

Use DBMS_STATS.CREATE_STAT_TABLE procedure and
DBMS_STATS.EXPORT_*_STATS procedures.
Idea is store the stats in a seperate table and import those stats to the table we want.

But i doubt whether it works as intended in your case.
>>TableB has same indexes as TableA except for the name. TableA has index1_A, index2_A.. and TableB has index1_B, index2_B..
This is a difference.
And
>>(col2 + 1) col2
If these columns are used , that may change the distribution of data. So everything is changed.
copying stats is intended only for pursposes said in the above docset.
Re: Copying statistics of one table to another [message #200272 is a reply to message #200268] Mon, 30 October 2006 03:16 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks.. I'll look into that.
Previous Topic: Find the Explain Plan from Trace File
Next Topic: datafile random read average time
Goto Forum:
  


Current Time: Tue Dec 06 10:32:04 CST 2016

Total time taken to generate the page: 0.13987 seconds