Multi‑value compression (MVC) is a form of logical data compression in which compressed values are stored in the table header not in the row itself (see “Teradata Database Mechanisms for Multi-Value Compression” on page 711). Each table has up to 1 MB of table header. MVC is a lossless method.
MVC is valid for permanent, global temporary, and volatile tables.
Besides storage capacity and disk I/O size improvements, MVC has the following performance impacts:
You may need to limit the number of values you compress depending on table specifications for factors such as journaling, fallback, logging, disk I/O integrity, and indexes.
You can use MVC to compress columns with these data types:
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')
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:
Note: Non-LOB distinct or structured UDTCompressing many values in a table can cause the dictionary cache to overflow. If this happens and your dictionary cache is less than the default value of 1 MB, increase your dictionary cache to 1 MB.
With multi-value compression, no decompression is required to access compressed data values. This removes the CPU cost tradeoff common to many compression implementations.
Note: You cannot apply MVC to row‑level security columns.
For more information about row‑level security, see Security Administration.
Specify options in CREATE TABLE and ALTER TABLE requests to control multi-value compression (for more information, see SQL Data Definition Language). You can compress column values using MVC individually or in combination with algorithmic compression.