15.00 - Storage of Compressed Values - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Storage of Compressed Values

The presence bits in the row header index into field 5 of the table header, where the compressed values are stored, once per column per AMP. This does not apply to algorithmically compressed data, which is stored in place within the row except for algorithmically compressed BLOB, BLOB‑related UDT, CLOB, CLOB‑related UDT, XML, XML‑related UDT, or Geospatial data that is generally stored in subtables. See SQL External Routine Programming for more information about algorithmic compression.

This is why the number of AMPs figures into the calculation of compression efficiency (see “Equation 6: Net Capacity Usage for Multi-Value Compression” on page 727).

Because the size of the table header is limited to 1 MB, there is a limit to how many bytes can be compressed for a given column. If the number of bytes compressed exceeds the maximum row length, then the CREATE or ALTER TABLE statement used to create the new table is not valid and the DDL statement aborts. This is true even if the number of values specified for compression does not exceed the upper limit of 255.

The following graph plots the number of compressible values that can be specified for a column as a function of column width.

Not surprisingly, the plot clearly indicates that the wider the column, the fewer the number of values that can be compressed for the column. Particularly for wider columns, this means that in order to optimize compression, you must carefully analyze your tables to determine which values occur the most frequently and then limit compression to the top n values from that list.