Teradata Columnar compression provides two primary advantages: a decrease in permanent storage utilization and disk input/output operations.
Part of the Teradata Compression and Space Management guide.
There is typically an advantage over row compression when compressing columns due to limited data variance per column. Teradata Columnar employs various compression methods that leverage this property.
Teradata Columnar & Run-Length Encoding
This compression technique stores each column value once and supplements it with data about the subsequent rows with the same value. For instance, if a date column contains ‘2015-10-29’ in rows 100-200, the run-length encoding method would record this information as follows:
‘2015-10-29’;100-200
Teradata Columnar & Dictionary Encoding
Dictionary encoding replaces repeated values with compact codes. Exact dictionary entry layout and per-container encoding are implementation-specific; the numeric codes below illustrate the idea, not a verified physical byte layout.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookValues for customers in the “Business” and “Private” segments can be saved as dictionary entries 1 and 2.
1, “Business”,2, “Private”
The column values will be stored as a sequence of alternating 1’s and 2’s, for example, 1, 2, 1, 2, 2, 2, 1, 1, 2, 1, and so on.
This mapping decreases disk space utilization by reducing the size of larger column values.
Teradata Columnar & Delta Compression
Teradata Columnar utilizes delta compression, a more sophisticated compression technique than the straightforward run-length and dictionary encoding methods.
Delta compression stores differences from a base value; that base need not be the mean. The following arithmetic example chooses mean 35 for illustration only.
10,20,50,100,20,10
Delta compression stores encoded information as follows (with an average column value of 35):
-25,-15,+15,+65,-15,-25
You may wonder about the benefit of using less space by replacing the original values with smaller numbers. The advantage lies in the data type. If the column container stores BIGINT values, the offsets can be saved as SMALLINT values, which frees up 6 bytes per row.
Conclusion
Columnar compression and row-store MVC suit different distributions and workloads. Neither universally wins; physical container encoding and compression ratios were not measured here.
Teradata determines the compression algorithm utilized, although this can be altered. Compression techniques can differ between table columns or containers within a column. Multiple approaches can even be employed simultaneously within each column.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book