Automatic compression of NULL values

Database
Teradata Employee

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
7 REPLIES
Enthusiast

Re: Automatic compression of NULL values

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.
Enthusiast

Re: Automatic compression of NULL values

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..
Enthusiast

Re: Automatic compression of NULL values

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

Re: Automatic compression of NULL values

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!
Enthusiast

Re: Automatic compression of NULL values

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.

Re: Automatic compression of NULL values

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

Re: Automatic compression of NULL values

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