Multi-value Compression (MVC)

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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