All Forums Database
30 Apr 2011
Automatic compression of NULL values

Hello!

I've been advised that V12 or V13 has added automatic compression of NULL values to fields that do not have explicit NOT NULL attributes. The effect here is that one would no longer need to add COMPRESS unless compressing actual values. Can anyone confirm this?

Thanks!

Andrew

Andrew Livingston EMEA - Customer Education Consultant Teradata UK 206 Marylebone Road London NW1 6LY United Kingdom M: +44 7785 971 080 E-mail: Andrew.Livingston@Teradata.com
Jim Chapman 449 posts Joined 09/04
30 Apr 2011

I don't think that feature exists in any release. The most significant compression enhancements are in 13.10, which provides column compression for variable fields, algorithmic compression, and block level compression. See the release summary for details.

For any kind of column level compression, you still need to specify the COMPRESS column attribute in the table definition.

enjoycoding 20 posts Joined 08/10
02 May 2011

I think by default Null value will not occupy significant space in the database file system. So you don't even need to compress null values. But I have seen Teradata allowing compress nulls in DDLs. No idea if my assumption is correct..

Jim Chapman 449 posts Joined 09/04
02 May 2011

By default, a null value consumes exactly the same space as a non-null value of the column type.

04 May 2011

EnjoyCoding - Jim is correct here. Effectively, Teradata is "earmarking" the space.

Jim - just wondering here. What would be the overhead/advisability of automatically compressing NULL values? I cannot think of any real issues, as Teradata could simply build it in as a feature of NULL values, that they are stored in the header instead of the actual table.

Obviously wouldn't want it working on primary index tables though.

Thanks all!

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

Jim Chapman 449 posts Joined 09/04
04 May 2011

There is a space-time performance trade-off. One advantage of "earmarking" the space, as you put it, is that the datum location within the row can be computed with minimal cost. Computing the location of a compressed column is more complex, and the cost increases as the column position relative to other compressed columns increases. For example, suppose a table contains 100 compressed columns. To locate the 100th column, it is necessary to compute the sum of the lengths of the preceding 99 columns.

16 Sep 2011

I have following values for a field in Teradata Table.

Account_ind     count(*)

1                    6120933182
?                    1818759486
0                    1227386046
                      70011215
Y                    4202360

While using compression can give the following syntax in create table statement?

 Account_Ind CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS (null, '1  ','0  ','Y  ','   ')

 

Or without specifying null, it will by default take the null values?

 

Hrishikesh
 

ZubinShams 1 post Joined 11/11
15 Jun 2012

TD 13.10 automatically compresses nulls when you specify COMPRESS. Not sure about prior versions.

Zubin

You must sign in to leave a comment.