Multi-value Compression (MVC) | Data Types and Literals | Teradata Vantage - 17.10 - Multi-value Compression (MVC) - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

You can compress data at the column level using multi-value compression, a lossless, dictionary-based compression scheme. With MVC, you specify a list of values to be compressed when defining a column in the CREATE TABLE/ALTER TABLE statement. When you insert a value into the column which matches a value in the compression list, a corresponding compress bit is set instead of storing the actual value, thus saving the disk storage space.

The best candidates for compression are the most frequently occurring values in each column. MVC is a good compression scheme when there are many repeating values for a column.

If MVC is not an efficient compression scheme for data in a particular column, you can compress the column using algorithmic compression (ALC). You can specify MVC alone, or both MVC and ALC on the same column. If you define both on the same column, ALC is applied only to those non-null values that are not specified in the value compression list of the MVC specification. You can also use MVC together with block-level compression (BLC).

You can use MVC to compress columns with these data types:
  • Any numeric data type
  • BYTE
  • VARBYTE
  • CHARACTER
  • VARCHAR
  • DATE
    To compress a DATE value, you must specify the value as a Date literal using the ANSI DATE format (DATE 'YYYY-MM-DD'). For example:
    COMPRESS (DATE '2000-06-15')
  • TIME and TIME WITH TIME ZONE
  • TIMESTAMP and TIMESTAMP WITH TIME ZONE
To compress a TIME or TIMESTAMP value, you must specify the value as a TIME or TIMESTAMP literal. For example:
COMPRESS (TIME '15:30:00')
COMPRESS (TIMESTAMP '2006-11-23 15:30:23')
In addition, you can use COMPRESS (NULL) for columns with these data types:
  • ARRAY
  • Period
  • Non-LOB distinct or structured UDT