All Forums Database
Chusa01 9 posts Joined 05/09
05 Oct 2011
how to make Compression Equal?!

when select inserting from one table to another -- where both have compression on the same column...  what does teradata do?  Does it need to decompress each & every column first -- to get the value and then attempt to insert it into the destination table by check each compressed value there & then storing it? 

Recently, we had a sql statement that got clobbered because is used up too much cpu time.  I think it had to do with the value compression -- but i'm not 100% sure..  so I'm wondering is there a better way to do this?  IF both tables had the exact same compression -- would the data remain compressed as Teradata evaluates it?  Would it *remap* data to it's new compressed value & not decompress it unless it has to?  (if my assumption on how it works is correct)?

ALSO -- if you're extracting data from one database across the network to another exactly same table structure... is there a way to retain the compressed values (instead of the actual values) -- so that the amount of spool and network traffic are minimized??  --> or carry the compression header across with you?

I'm open to any ideas.

thanks!

-Chuck

Adeel Chaudhry 773 posts Joined 04/08
11 Oct 2011

Hi,

 

The CPU consumption can surely be due to uncompressing and recompressing the values.

 

Regarding moving data from one database to another .... if the only concern is to minimize the spool and network traffic .... you can use backup/recovery option.

 

HTH!

 

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

Chusa01 9 posts Joined 05/09
12 Oct 2011

really appreciate the reply. 

backup/recovery won't work -- i need a dynamic solution & somthing that doesn't land the data;  i don't have the means to support files of an *unknown* size.  By using the backup & restore, I would limit our ability to move data by whatever space is availble on disk.  We Stream it across from one box to another so that i doesn't need to land anywhere.  The problem is thou, that it expands everything AS you extract it...  so it takes mroe spool & more network traffic to push it across.

i wonder if there's any way to get to the *internal-index* of the compression value in the header, so that i could push that byte across instead of the whole value?  i could create a big huge case statement, but that might get me into trouble with CPU usage.

 

VandeBergB 182 posts Joined 09/06
12 Oct 2011

Is the PI and partitioning the same on the source and target tables?  What version of Teradata are you running this on?

 

Some drink from the fountain of knowledge, others just gargle.

Jim Chapman 449 posts Joined 09/04
12 Oct 2011

It is possible to run a backup and restore (or copy) operation in parallel using a named pipe or Unix FIFO as the intermediate archive.  This is a commonly used technique to avoid landing the intermediate data stream on disk.

Adeel Chaudhry 773 posts Joined 04/08
13 Oct 2011

I do agree with Jim's statement, can you please confirm it will serve your purpose?

 

Regards, MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.