17.10 - Autocompression - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

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

Benefits of Autocompression

  • Default for column partitions. If you change the default to NO AUTO COMPRESS or you specify NO AUTO COMPRESS for the column partitioning, you can specify AUTO COMPRESS for a column partition to have that column partition autocompressed.
  • Vantage can choose to apply multiple autocompression methods from a list of available techniques depending on their effectiveness for a given column partition (note that currently there are no available techniques for column partitions with ROW format).
  • Reduces I/O overhead if the data can be compressed.
  • Vantage only needs to decompress autocompressed column partition values that are accessed, and decompression is automatically done with typically very little performance overhead when they are retrieved.
  • Easily combined with null, multivalue, algorithmic, row, block-level, and temperature-based block-level compression.
  • Has no effect on table header size.

Best Use of Autocompression

  • Column partitions with a single column and COLUMN format

Restrictions and Limitations of Autocompression

  • There is CPU overhead in determining whether or not a physical row should be compressed and in determining what compression techniques to use if it is to be compressed.

    This overhead is usually minimal, but you can eliminate it by specifying the NO AUTO COMPRESS option for a column partition or the column partitioning (or change the default to NO AUTO COMPRESS), which eliminates autocompression for that column or column partition.

  • There is additional CPU overhead when you insert rows into a column-partitioned table that uses autocompression.
  • If you specify autocompression, Vantage might not apply user-specified MVC or algorithmic compression if they do not further reduce storage space.
  • There are no autocompression techniques applied to a column partition with ROW format, even when AUTO COMPRESS is the default for the column partition or you specify it directly.