15.00 - Common Rules for Both Multi-Value and Algorithmic Compression - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Common Rules for Both Multi-Value and Algorithmic Compression

With standard multi-value compression, there is essentially no decompression necessary to access compressed data values. 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 both multi-value and algorithmic compression.

  • You can specify multi-value compression alone, algorithmic compression alone, or both for table columns.
  • If you specify both multi-value and algorithmic compression for a column, you can specify them in any order: multi-value compression followed by algorithmic compression or algorithmic compression followed by multi-value compression.
  • When you specify both multi-value compression and algorithmic compression on the same column, Teradata Database applies algorithmic compression only to values that are not also specified for multi-value compression.
  • Specifying either algorithmic compression alone or both algorithmic and multi-value compression for a column has the following data type size limitations.
  •  

    IF you specify this type of compression for a column …

    THEN the data type size …

    algorithmic only

    has no limit.

    algorithmic and multi-value

    is limited by the maximum valid size for multi-value compression.

  • You cannot specify either multi-value compression or algorithmic compression on a column that is a component of the primary index for a table.
  • You can specify both multi-value compression and algorithmic compression on a column that is a component of a secondary index for a table.
  • You can specify either multi-value compression or algorithmic compression or both on a column that is a component of a referential integrity constraint.
  • You can specify either multi-value compression or algorithmic compression or both on the columns of unpartitioned data tables and global temporary tables.
  • Both algorithmic compression and multi-value compression are supported by default for the columns of spool files. If a column is defined with either multi-value compression, algorithmic compression, or both, the compression defined for the column is inherited for the spool file column.
  • You can copy the definition of a table to a different name using the COPY TABLE … AS option, and the target table retains any algorithmic or multi-value compression that are defined for the source table.