All Forums Database
arpitsinha 13 posts Joined 06/15
10 Jul 2015
Backup and Restore only statistics in Teradata

Hi,

 

Actually I am new to Teradata; 

 

I Just wanted to know if there is a way to restore old statistics or copy only statistics from table A to Copy of table A...?

Regards, Arpit

Glass 225 posts Joined 04/10
10 Jul 2015

Arpit,
You can restore old stats if you copy them to a copy of the table (with or without data).
collect stats on database.table_a_copy from database.table_a;
to restore: collect stats on database.table_a from database.table_a_copy;
I'm not sure why you would want to do this because if you restore a table using ARC, stats are also restored and if you create a copy of the table you can specify 'with stats'.
 
Rglass

arpitsinha 13 posts Joined 06/15
11 Jul 2015

Hi Glass,
 
Thanks, Got it.
If there is a way I can take backup of statistics of a table_A so that if I refresh the statistics of table_A I have always a backup of stats which i can restore again if the new stats is making some queries to change plan or some how impacting the performance.
 

Regards, Arpit

abhishek.jadhav 28 posts Joined 02/08
17 Jul 2015

Arpit,
You can extract/save the statistics information using the below statements

Show statistics values column (column_name) On DB_name.Table_name;

 

Show summary statistics values on DB_name.Table_name;

Thanks
Abhishek Jadhav

kirthi 65 posts Joined 02/12
17 Jul 2015

We can copy stats from from one table to another only if the stuctures are exactly same.
copy stats feature can be really helpful when you are trying to alter a huge table. we really need not go for the cosly process of re-collecting the stats after the alter.
below is a simple implemenation of the same suppose you want to change the table (A_TBL)
CT A_tbl_new from A_tbl with no data and stats;
collect stats on A_tbl_new from A_tbl;
Now we can alter the empty new table (A_tbl_new)  with ease,
after which we can populate the A_tbl_new data from old table and apply rename to A_tbl to A_tbl_old and rename the A_tbl_new to A_tbl.
Drop the A_tbl_old

Proactive 10 posts Joined 07/14
01 Oct 2015

Hi Glass,
I have copied tables from production to development but stats are not being copied and it is telling "there are no statistics defined" but the table in production has stats defined on it.
 
Is there any specific syntax for copy stats using arc?
 

Glass 225 posts Joined 04/10
01 Oct 2015

Arpit,
If you "copy" using Arc utility you wll lose stats, If you "restore" the stats are also restored.
If you use Datamover to copy include:
<table selection="included" copyStats="true">
 
 
Rglass

abhishek.jadhav 28 posts Joined 02/08
01 Oct 2015

If you use “Copy” in arcmain and table name remains same then stats is also copied.

If you rename the table in your copy script then “copy” will not bring over the stats information from source system.

Thanks
Abhishek Jadhav

stami27 11 posts Joined 06/09
13 Apr 2016
We have a proven method for the transmission of statistics. 
We can  copy statistics to another table from a table even when the structures is not the same.
It is immaterial whether the copy of the table gets new attributes 
or attributes have changed
If an attribute in the new table is cleared, 
then an error is returned, but the transfer continues.


1)Create mytable_x1_tmp as mytable_x1 for example with Attribute Changes or some new Attributes etc.
2)Insert the Data from mytable_x1 to mytable_x1_tmp
3)Then in following  bteq (sample piece of script):
...
....
--- Export stats to a File
.os rm /tmp/t_set_stats_mytable_x1
.export report file /tmp/t_set_stats_mytable_x1
SHOW STATISTICS VALUES ON $DBX_DATA.mytable_x1;

.export reset
---------------------------------------------------------------------------
-- 1: Rename orig. Table to a save table
----------------------------------------------------------------------------
RENAME TABLE $DBX_DATA.mytable_x1 TO $DBX_DATA.mytable_x1#backup;

.if      ERRORCODE        <>               0 then .QUIT ERRORCODE

----------------------------------------------------------------------------
-- 2: Rename new table mytable_x1_tmp to orig. table
----------------------------------------------------------------------------
RENAME TABLE $DBX_DATA.mytable_x1_temp TO $DBX_DATA.mytable_x1;

.if      ERRORCODE        <>               0 then .QUIT ERRORCODE

----------------------------------------------------------------------------
-- 3: import statistics of the original table into mytable_x1
----------------------------------------------------------------------------
.run file /tmp/t_set_stats_mytable_x1
.os rm /tmp/t_set_stats_mytable_x1
....
....

 

You must sign in to leave a comment.