16.20 - Common Rules for Multivalue and Algorithmic Compression - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

With standard multivalue compression, compressed data values can be accessed without decompression processing overhead. Teradata Database does not need to decompress blocks or other large chunks of data to be able to access a single row or value. This removes the significant CPU cost tradeoff common to most compression implementations.

The following rules apply to multivalue and algorithmic compression.

  • For table columns, you can specify multivalue compression, algorithmic compression, or both.
  • If you specify multivalue and algorithmic compression for a column, you can specify the options in any order: multivalue compression followed by algorithmic compression or algorithmic compression followed by multivalue compression.
  • When you specify multivalue compression and algorithmic compression on the same column, Teradata Database applies algorithmic compression only to values that are not also specified for multivalue compression.
  • Specifying algorithmic compression alone or in combination with multivalue compression for a column has the following data type size limitations:
    Type of Column Compression Data Type Size
    Algorithmic only Has no limit.
    Algorithmic and multivalue Limited by the maximum valid size for multivalue compression.
  • You cannot specify multivalue compression or algorithmic compression on a column that is a component of the primary index or primary AMP index for a table.
  • You can specify multivalue compression and algorithmic compression on a column that is a component of a secondary index for a table.
  • You can specify multivalue compression, algorithmic compression, or both on a column that is a component of a referential integrity constraint.
  • You can specify multivalue compression, algorithmic compression, or both on the columns of nonpartitioned data tables and global temporary tables.
  • Algorithmic compression and multivalue compression are supported by default for the columns of spools. If a column is defined with multivalue compression, algorithmic compression, or both, the compression defined for the column is inherited for the spool column.
  • You can copy the definition of a table to a different name using the COPY TABLE … AS option, and the target table retains the algorithmic or multivalue compression defined for the source table.