Teradata Database 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.
- JSON, with some restrictions listed below
- DATASET, with some restrictions listed below
- TIME and TIME WITH TIME ZONE
- TIMESTAMP and TIMESTAMP WITH TIME ZONE
- Period types
- Distinct UDTs, with some restrictions listed below
- System-defined UDTs, with some restrictions listed below
- 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);
- Specify the name of an ALC decompression UDF in the DECOMPRESS USING phrase for the column definition.
- Specify only one set of compression/decompression UDFs for a particular column.
- The system automatically compresses nulls when you specify COMPRESS.
|For information on...||See...|
|functional details, usage requirements, and restrictions||“Compression/Decompression Functions” in SQL Functions, Operators, Expressions, and Predicates|
These restrictions apply:
- You cannot use ALC to compress columns that have a data type of structured UDT.
- The TD_LZ_COMPRESS and TD_LZ_DECOMPRESS system functions compress all large UDTs including UDT-based system types such as Geospatial, XML, and JSON. However, if you write your own compression functions, the following restrictions apply:
- Custom compression functions cannot be used to compress UDT-based system types (except for ARRAY and Period types).
- Custom compression functions cannot be used to compress distinct UDTs that are based on UDT-based system types (except for ARRAY and Period types).
- You cannot write your own compression functions to perform algorithmic compression on JSON type columns. However, Teradata provides the JSON_COMPRESS and JSON_DECOMPRESS functions that you can use to perform ALC on JSON type columns.
- You cannot write your own compression functions to perform algorithmic compression on DATASET type columns. However, Teradata provides the SNAPPY_COMPRESS and SNAPPY_DECOMPRESS functions that you can use to perform ALC on DATASET type columns.
- You cannot use ALC to compress temporal columns:
- A column defined as SYSTEM_TIME, VALIDTIME, or TRANSACTIONTIME.
- The DateTime columns that define the beginning and ending bounds of a temporal derived period column (SYSTEM_TIME, VALIDTIME, or TRANSACTIONTIME).
You can use ALC to compress Period data types in columns that are nontemporal; however, you cannot use ALC to compress derived period columns.
For details about temporal tables, see Temporal Table Support and ANSI Temporal Table Support.
- You cannot specify multivalue or algorithmic compression for a row-level security constraint column.
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);
|For information on...||See...|
|specifying a UDF in the COMPRESS USING phrase of a CREATE TABLE or ALTER TABLE statement||
|rules for using compression/decompression algorithms in UDFs||the section on “User-Defined Functions” in SQL External Routine Programming|
|System-defined external UDFs for algorithmic compression and decompression||SQL Functions, Operators, Expressions, and Predicates|
|How to code scalar UDFs to perform algorithmic compression on column data||SQL External Routine Programming|
|How to create the SQL definition for an algorithmic compression UDF||CREATE FUNCTION (External Form) in SQL Data Definition Language|
|How to specify those scalar UDFs in a table definition||ALTER TABLE and CREATE TABLE in SQL Data Definition Language|
|Guidelines for selecting an algorithmic compression functions||http://developer.teradata.com/extensibility/articles/selecting-an-alc-compression-algorithm|
|Evaluating algorithmic compression UDFs||download the test suite from http://downloads.teradata.com/download/extensibility/algorithmic-compression-test-package|