Teradata - Compression

Compression is used to reduce the storage used by the tables. In Teradata, compression can compress up to 255 distinct values including NULL. Since the storage is reduced, Teradata can store more records in a block. This results in improved query response time since any I/O operation can process more rows per block. Compression can be added at table creation using CREATE TABLE or after table creation using ALTER TABLE command.

Limitations

Only 255 values can be compressed per column.

Primary Index column cannot be compressed.

Volatile tables cannot be compressed.

Multi-Value Compression (MVC)

The following table compresses the field DepatmentNo for values 1, 2 and 3. When compression is appped on a column, the values for this column is not stored with the row. Instead the values are stored in the Table header in each AMP and only presence bits are added to the row to indicate the value.

CREATE SET TABLE employee ( 
   EmployeeNo integer, 
   FirstName CHAR(30), 
   LastName CHAR(30), 
   BirthDate DATE FORMAT  YYYY-MM-DD- , 
   JoinedDate DATE FORMAT  YYYY-MM-DD- , 
   employee_gender CHAR(1), 
   DepartmentNo CHAR(02) COMPRESS(1,2,3) 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

Multi-Value compression can be used when you have a column in a large table with finite values.