17.10 - Multivalue Compression - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

This section summarizes the benefits, best uses, and restrictions of multivalue compression in Vantage.

Benefits of Multivalue Compression

  • Minimal performance decrement for compressing or decompressing values
  • Reduced I/O for table scans, spooling result sets, and data loads
  • Reduced I/O for cached tables because more smaller tables can be cached, eliminating I/O operations on those tables
  • Valid for most commonly used data types
  • Spools retain the multivalue compression of their base tables
  • Can be specified for permanent, global temporary, and volatile table columns
  • Automatically compresses nulls
  • Easily and complementarily combined with algorithmic compression
  • Easily combined with block-level and temperature-based block-level compression and autocompression for column-partitioned tables and join indexes

Best Use of Multivalue Compression

  • Very wide columns
  • Columns with few distinct values
  • Columns in large tables
  • Tables having a large number of rows with repeating values
  • Tables having a large number of rows with default values
  • Columns having a constant distribution of values across time

Restrictions and Limitations of Multivalue Compression

  • Requires extensive data analysis prior to implementation
  • Can compress a maximum of only 255 different characters per column
  • The more values compressed per row, the more presence bits in the row header are required, which increases the row size
  • Affects table header size
  • Cannot specify for primary index or primary AMP index columns
  • Cannot for the partitioning columns of a partitioning expression
  • Can specify on secondary index columns, but the column data in the secondary index subtable is not multivalue compressed
  • Columns with many unique or nearly unique values cannot benefit from multivalue compression
  • A given multivalue compression scheme can become less useful across time if the values in the table change significantly