how to make Compression Equal?!

Database
Enthusiast

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

5 REPLIES
Teradata Employee

Re: how to make Compression Equal?!

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

Enthusiast

Re: how to make Compression Equal?!

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.

Enthusiast

Re: how to make Compression Equal?!

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

Enthusiast

Re: how to make Compression Equal?!

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.

Teradata Employee

Re: how to make Compression Equal?!

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

Regards, MAC