Multi-Value compression has increased the table size

Database
Enthusiast

Re: Multi-Value compression has increased the table size

Does a limit exist for the number of charcters stored in the row header?  A user has DDL that fails (CREATE TABLE Failed. 5627:  Column 'VAR28' exceeds system limit for COMPRESS.)

The column compression statement in question has 172 unique values but the compression values (including single quotes and commas) add up to 7257 characters. 

Enthusiast

Re: Multi-Value compression has increased the table size

A Row Header has no limit as such but the Maximum row size has a limit to an extent which could fit into a DATA BLOCK.

TD has a max block size of 64 KB

Data Block Size(TD 12)  = (DATABLOCK HEADER + ROW DATA + REFERNCE ARRAY + DATABLOCK TRAILER) = 72 bytes (for new and updated tables - (36 for Ver6.2))

so a row in essence could not grow beyond 64 KB - 72 bytes.

Also it depends upon the max data block size defined by your DBA.

Hope it helps.

Manik

Enthusiast

Re: Multi-Value compression has increased the table size

Thanks Manik, I appreciate your response.

Teradata Employee

Re: Multi-Value compression has increased the table size

There are two limits that apply. There is a maximum number of bytes for the compression values for a single column controlled by the CompresValueList column in TVFields dictionary table (8192 bytes currently). This is likely the cause of your 5627 error. Separately there is a grand total number of bytes that can fit in a table header. This contains all compression values for all columns, index definitions, column definitions, partition definitions, format strings,... It is not possible to say an exact number for the limit of total compression values because it depends on the size of all the other parts of the table definition. For instance, a very wide table with lots of columns and indexes will be able to accomodate less compression values than a narrower table with less other structures associated with it. Total table header size is currently 1MB.

Highlighted
Enthusiast

Re: Multi-Value compression has increased the table size

If you are running on a 64 bit system you may also be running into a 8 byte alignment requirement.

Enthusiast

Re: Multi-Value compression has increased the table size

Hi Dieter,

I proposed a MVC to my client for Teradata 12 Tables. As per the analysis, I got around 800 GB approx saving on 2.3 TB of tables giving table level and column level savings. After too much of investigation on MVC, Client has come up with a concern as below:

Concern: Some of these columns are derived from bases that can change e.g. pricing strategies, cost price changes, tax (vat).

If any of these bases change the profile of the data in the tables will change, which means that a totally new set of ‘ideal’ compression values would apply.

How often would the compression values be reviewed?

As per my understanding , If the column values are more volatile for derived columns then we do not suggest applying the compression

But if columns values are more duplicate and static then apply the compress to save the space.  But on the whole ,  I am still confused that even if the columns are derived, but I somehow still got the savings for that table, around 30%-40 %.

Can you please advise , if there is a way, we can apply compression on tables with some some/all derived columns, as i can see much saving ..

Regards,

Amit

Enthusiast

Re: Multi-Value compression has increased the table size

Hi,

Can you please help me with an update if above concern looks meaningful.

Regards,

Enthusiast

Re: Multi-Value compression has increased the table size

hi

what is the max number of value per column that can be compressed using MVC in TD14.

Enthusiast

Re: Multi-Value compression has increased the table size

You can read the doc :)

You cannot compress more than 255 distinct values for an individual column.

There are few more guidelines too.

Enthusiast

Re: Multi-Value compression has increased the table size

Hi,

I wanted to know how much i can save using the MVC on multiple columns in a table. 

I am taking a example. In TD 12 I am having a table A of 50 GB.

Going to compress demical(18,2) columns. Like this col1 - 15 values represents 15% of table data. Col2 - 255 values represents 35% of table data. If i apply compression on both these columns how much can i save? Is there any formula to calculate the savings because of compression? Please help.