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:
Compression can be used for the following reasons.
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.


Table Header
'MANAGER','SYSTEM ANALYST','SALESMAN','LDC'

Emp_No Job_Title Presence Bits
1000310 SALESMAN 00000011
1000336 LDC 00000100
1000299 SALESMAN 00000011
1000262 MANAGER 00000001
1000292 SYSTEM ANALYST 00000010

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)