Common Rules for Multivalue and Algorithmic Compression - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

With standard multivalue compression, compressed data values can be accessed without decompression processing overhead. Vantage 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, Vantage 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.