Backup and Restore only statistics in Teradata

Database
Enthusiast

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...?

8 REPLIES
Enthusiast

Re: Backup and Restore only statistics in Teradata

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

Enthusiast

Re: Backup and Restore only statistics in Teradata

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.

Junior Supporter

Re: Backup and Restore only statistics in Teradata

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;


Abhishek Jadhav
Enthusiast

Re: Backup and Restore only statistics in Teradata

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

Enthusiast

Re: Backup and Restore only statistics in Teradata

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?

Enthusiast

Re: Backup and Restore only statistics in Teradata

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

Junior Supporter

Re: Backup and Restore only statistics in Teradata

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.


Abhishek Jadhav
Enthusiast

Re: Backup and Restore only statistics in Teradata

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
....
....