Guidelines for Using Multivalue Compression - Teradata Database

Guidelines for Using Multivalue Compression

To change the values that are compressed or to change whether or not a column uses compression, use an ALTER TABLE request. For example, the following turns off the compression in the mycol_varchar column:

ALTER TABLE DB.mytable_vlc

ADD mycol_varchar NO COMPRESS;

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

You cannot create a table with more bytes compressed than there is room to store them in the table header.

The maximum number of characters that can be listed in a COMPRESS clause is 8,188.

You cannot compress values in columns that are any of the following:

Primary index columns

Identity columns

Derived table columns

Derived period columns

Row‑level security constraint columns

Referenced primary key columns

Referencing foreign key columns for standard referential integrity relationships

You can compress values in referencing foreign key columns for Batch and Referential Constraint referential integrity relationships.

You can compress columns that are a component of a secondary index, but MultiLoad operations on a table with a secondary index can take longer if the secondary index column set is compressed.

To avoid this problem, drop any compressed secondary indexes before starting the MultiLoad job and then recreate them afterward.

You can compress columns that are components of a referential integrity relationship.

You can assign multivalue compression to columns that contain the following types of data:

Nulls, including nulls for distinct and structured non‑LOB and non‑XML UDTs, ARRAY/VARRAY and Period UDT data types

Zeros

Blanks

Constants having any of the data types supported by multivalue compression.