15.00 - Guidelines for Using Multi-Value Compression - Teradata Database

Teradata Database Design

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

Guidelines for Using Multi-Value Compression

  • To change the values that are compressed or to change whether or not a column uses compression, use an ALTER TABLE request. For example, the following turns off the compression in the mycol_varchar column:
  • ALTER TABLE DB.mytable_vlc
    ADD mycol_varchar NO COMPRESS;
  • You cannot compress more than 255 distinct values for an individual column.
  • You cannot create a table with more bytes compressed than there is room to store them in the table header.
  • The maximum number of characters that can be listed in a COMPRESS clause is 8,188.
  • You cannot compress values in columns that are any of the following:
  • Primary index columns
  • Identity columns
  • Derived table columns
  • Derived period columns
  • Row‑level security constraint columns
  • Referenced primary key columns
  • Referencing foreign key columns for standard referential integrity relationships
  • You can compress values in referencing foreign key columns for Batch and Referential Constraint referential integrity relationships.

  • You can compress columns that are a component of a secondary index, but MultiLoad operations on a table with a secondary index can take longer if the secondary index column set is compressed.
  • To avoid this problem, drop any compressed secondary indexes before starting the MultiLoad job and then recreate them afterward.

  • You can compress columns that are components of a referential integrity relationship.
  • You can assign multi-value compression to columns that contain the following types of data:
  • Nulls, including nulls for distinct and structured non‑LOB and non‑XML UDTs, ARRAY/VARRAY and Period UDT data types
  • Zeros
  • Blanks
  • Constants having any of the data types supported by multi-value compression. See “Multi‑Value Compression” on page 699.