Algorithmic Compression | Database Design | Teradata Vantage - Algorithmic Compression - Advanced SQL Engine - Teradata Database

Vantage software includes several standard compression algorithms, in the form of UDFs, which you can use to compress many types of data by table column. You can also create custom compression and decompression algorithms in UDF format.

When column values are mostly unique, algorithmic compression (ALC) may provide better compression results than MVC. When columns have repeated values, you can use ALC and MVC concurrently on the same column, but the system does not apply ALC to any value covered by MVC.

ALC generally functions best on cold (seldom used) data because of the amount of CPU required for decompress/recompress when compressed data is accessed, although some ALC algorithms require less CPU than others. ALC is considered to be the most difficult to implement of all the compression methods.

Procedure

1. Specify the name of an ALC compression UDF in the COMPRESS USING phrase for a column definition in a CREATE TABLE or ALTER TABLE statement, for example:

   CREATE TABLE  table_name 
     (ItemNo INTEGER,
      Gem CHAR(10) UPPERCASE,
      Description VARCHAR(1000)
         COMPRESS USING TD_SYSFNLIB.compression_UDF_name 
         DECOMPRESS USING TD_SYSFNLIB.decompression_UDF_name);

2. Specify the name of an ALC decompression UDF in the DECOMPRESS USING phrase for the column definition.

Usage Notes

• Specify only one set of compression/decompression UDFs for a particular column.
• The system automatically compresses nulls when you specify COMPRESS.

For information about functional details, usage requirements, and restrictions on compression and decompression functions, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

Restrictions

You can apply algorithmic compression to referential integrity columns.

Depending on the implementation, algorithmic compression can be either physical or logical, though most implementations use physical data compression. Algorithmic compression can be either lossy or lossless, depending on the algorithm used.

Using Custom ALC Algorithms

You can implement a custom compression algorithm as a scalar external UDF, and then specify the UDF in the COMPRESS USING phrase of a column definition for a CREATE TABLE or ALTER TABLE statement, for example:

CREATE TABLE  table_name 
  (ItemNo INTEGER,
   Type CHAR(10) UPPERCASE,
   Description VARCHAR(1000)
      COMPRESS USING  compression_UDF_name 
        DECOMPRESS USING  decompression_UDF_name);

Related Information