Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 13 Oct 2006 @ 20:38:39 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Way to Copy Statistics.
From:   Barner Eric

Ok , here's a doozy.

I am currently writing a set of SQL Procs to automate table / statistics replication. And I am researching how to do the following.

If I can find a solution , I will provide the forum with the procedures and mehodology.

I want to know if there is a way to in effect 'COPY' stats from one table to another (perhaps at OS level).

Here is the SCENARIO.

Example I have 2 tables.

TableX (Production table).

TableX_Mv (Empty Clone of production table).

Currently we drop all secondary indices on TableX_Mv in order to MLOAD quickly. (This is really an mload and a merge/coalesce) process.

Once all of the batch jobs are done we need to rebuild NUSI's and recollect Stats on TABLEX_MV.

Then rename TABLEX to TABLE_OLD as it is replaced.

And rename TABLEX_MV to TABLEX as it is now the production table.

Here is the PROBLEM.

And as we all know recollecting stats all the time on NUSI's and multicolumn stats is freaking slow, and taxes the system heavily (Tons of I/O, spool etc.), and is generally not a good idea.

The data does not change that much in volume and I don't care if the stats are that up to date.

So.. I would like to know

1.) Is it possible to copy the statistics from (dbc.TVFields, dbc.INDEXES) where they are stored and impose them on another table?

Basically is there a way to clone the table (statistical info and all) with out loading data and issuing the collect stats and deleting all the data?

2.) If yes, can this be done with SQL, or only with Unix scripting, or use of NCR tools (maybe Filer?);

3.) Has anyone actually done something like this (or modified Stats in an unconventional way)? And would they be kind enough to share their experience.

Thanks in advance to all for their time considering and responding to this request!

Eric W. Barner
Senior Teradata DBA
Information Technology & Services

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020