15.00 - Multi-Value Compression - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Multi‑Value Compression

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:

  • Improves table scan response times for most configurations and workloads
  • Provides moderate to little CPU savings
  • 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:

  • 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
  • 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.