This section summarizes the benefits, best uses, and restrictions of autocompression in Teradata Database.
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.
- Teradata Database 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.
- Teradata Database 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.
- System-wide default AUTO COMPRESS or NO AUTO COMPRESS setting is controlled by the AutoCompressDefault cost profile constant.
- 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, Teradata Database 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.