Algorithmic Compression (ALC), a new compression technique introduced in Teradata 13.10, is mainly designed for compressing data with well-know columns. Teradata provides a few built in ALC algorithms. For example, to compress Unicode column data Teradata provided TransUnicodeToUTF8 algorithm. Apart from that you can also develop your own customize algorithm to compress data.
The compression algorithm is invoked during creation of the data block from the table’s data and the same algorithm is used to decompress the data when any user wants to access any value from that particular column. The decompression algorithm will not use on those columns which are not accessed.
Unlike multi value compression (MVC), ALC requires CPU resources to execute the compression and decompression algorithm.
Defining ALC in Teradata Tables
Same as MVC, ALC is also defined on column level. We can define ALC in Teradata column using CREATE TABLE statement.
CREATE TABLE Student
(Roll_No INTEGER,
Student_Name VARCHAR(50),
Student_Address CHAR(200) CHARACTER SET UNICODE
COMPRESS USING TransUnicodeToUTF8
DECOMPRESS USING TransUTF8ToUnicode)
UNIQUE PRIMARY INDEX(Roll_No);
In the above example, column Student_Address is compressed using the packaged TransUnicodeToUTF8 algorithm.
Both ALC and MVC can be defined on the same column. In this case, ALC will be applied only to those values that are not specified by MVC. ALC will be applicable only on column with CHAR, VARCHAR and BYTE data type.
Similarities between ALC and MVC
- Applied on column level.
- Cannot be defined on primary index column.
- Can be specified on secondary index although will be not applied to index subtable.
- Supported on permanent as well as global temporary tables.
- Not supported on volatile tables
- All NULLS are automatically compressed.
Differences from MVC
Key differences in functionality between ALC and MVC are as below-
- MVC can be applied on max 255 values per column wherein ALC can be applied in all the values irrespective of what their particular value is.
- ALC is limited to the CHAR, VARCHAR and BYTE data type while MVC can incorporate all the numeric as well as date data types.
- There is overhead involved to compress and decompress data in ALC, but in case of MVC there is no such kind of overhead.
Recommendation for using ALC
Algorithmic Compression is very useful for large character column which carries mainly descriptive information and accessed less frequently.
Unicode columns are another good candidate for ALC as Unicode takes more space to represent same value compare to Latin. For example, in above Student table, Student_Address is a good candidate for ALC.