Teradata Multivalue Compression (MVC) allows the users to compress the most commonly appearing values in one or more columns of teradata table

Teradata Multivalue Compression (MVC)

Multivalue compression(MVC) is widely used in Teradata,It allows the users to compress the most commonly appearing values in one or more columns. MVC uses a dictionary to maintain value of data and its corresponding bit pattern

Multivalue compression characteristics:

• MVC works at column level and should be defined for each column explicitly for the required columns.
• Maximum 255 distinct values (plus NULL) can be compressed per column.Below error will be thrown if user try to compress more than 255 values.

  
  *** Failure 3706 Syntax error: Too many compress values.
  

• MVC works with Multiset & Set tables.
• MVC works with permanent,volatile & global temporary tables.
• MVC works on numeric as well varchar(including char) columns.
• MVC cannot be used on a primary index column or partitioning expression column.Below error will be thrown if user try to do so.
  

  
  *** Failure 3623 The user cannot use COMPRESS on a primary index column or partitioning expression column.
  

• Alter table can be used to define Compression on existing/new columns.
  

  
  ALTER TABLE tutorial_db.employee ADD job_title VARCHAR(30) COMPRESS ('MANAGER','SYSTEM ANALYST','SALESMAN','LDC'),
                                   ADD dept_no INTEGER COMPRESS(100,200,300))
  

Compression can be used for the following reasons.

• To reduce storage costs
• To enhance system performance by moderate to little CPU saving

How Compression works in Teradata

Teradata stores all the values specified within Compress clause in the table header and the values are stored in the form of 8 digit bits for compressed columns.This is how it is saving space.

Explanation : As shown in the table above for table employee (column Job_Title), presence bits(3rd column) will be actually stored in the rows for this column and actual values are stored in table header

Example 1: Table creation with MVC

CREATE MULTISET TABLE tutorial_db.employee(
  emp_no INTEGER,
  emp_name VARCHAR(50),
  job_title VARCHAR(30),
  manager_id INTEGER,
  hire_date Date,
  salary DECIMAL(18,2),
  commission DECIMAL(18,2),
  dept_no INTEGER COMPRESS(100,200,300)
)
PRIMARY INDEX(emp_no)