17.10 - COMPRESS USING - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Algorithmic compression (ALC) for the column.

You can combine multivalue compression, algorithmic compression, and block-level compression on a table for better compression.

To avoid performance impact on other workloads, you should not use algorithmic compression with block-level compression.

For guidelines on creating algorithmic compression UDFs for UDT, BLOB, CLOB, XML, and Geospatial columns, see CREATE FUNCTION (External Form) in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184 and Teradata Vantage™ - SQL External Routine Programming, B035-1147.

The rules that apply to multivalue compression also apply to algorithmic compression.

Algorithmic compression is not supported for row-level security constraint columns.

If you specify COMPRESS USING, you must also specify DECOMPRESS USING. You can specify COMPRESS USING and DECOMPRESS USING in either order.

The default containing database for compress_UDF_name is SYSUDTLIB. If compress_UDF_name is an embedded services UDF, then its default containing database is TD_SYSFNLIB. If Vantage cannot find compress_UDF_name in either database, the system returns a message to the requestor.

You can specify multivalue compression and algorithmic compression for the same column. Algorithmic compression is only applied to values that are not specified for multivalue compression.

You can specify multivalue compression and algorithmic compression in either order.

For database object naming rules, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

For more information about algorithmic compression, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184 and Teradata Vantage™ - Database Design, B035-1094.

You can also specify COMPRESS USING for a column in a volatile table.

compress_UDF_name
Name of the UDF to be used to algorithmically compress data in this column. For a list of data types that can be algorithmically compressed, see the COMPRESS and DECOMPRESS phrases in Teradata Vantage™ - Data Types and Literals, B035-1143.
database_name
Default containing database for compress_UDF_name is SYSUDTLIB. If compress_UDF_name is an embedded services UDF, then its default containing database is TD_SYSFNLIB. If compress_UDF_name is not contained in SYSUDTLIB or TD_SYSFNLIB, the system returns an error to the requestor.

Example: Specifying Algorithmic Compression

The first example in this set specifies algorithmic compression (ALC) only for col_2. The UDF compress_udf compresses all values of col_2 and the UDF decompress_udf decompresses them.

CREATE TABLE Pendants (
  col_1 INTEGER, 
  col_2 CHARACTER(10) COMPRESS USING compress_udf 
                      DECOMPRESS USING decompress_udf);

The following example specifies algorithmic compression and multivalue compression for col_2, with multivalue compression specified first. The UDF compress_udf compresses only those values that are not specified in the multivalue list. The UDF decompress_udf decompresses the values compressed by compress_udf.

CREATE TABLE Pendants (
  col_1 INTEGER, 
  col_2 CHARACTER(10) COMPRESS ('amethyst','amber') 
                      COMPRESS USING compress_udf 
                      DECOMPRESS USING decompress_udf);

The following example specifies algorithmic compression and multivalue compression for col_2. Algorithmic compression is performed using the UDF compress_udf and algorithmic decompression is performed using the UDF udf_decompress.

CREATE TABLE Pendants (
  col_1 INTEGER, 
  col_2 CHARACTER(100) COMPRESS  ('amethyst', 'amber') 
                 COMPRESS USING compress_udf 
                 DECOMPRESS USING decompress_udf);

This example copies the compression specified for table t1 to table t2.

CREATE TABLE t2 AS t1 WITH NO DATA;

Example: Specifying Algorithmic Compression for a Column with the TIMESTAMP Data Type

The following example specifies algorithmic compression (ALC) and decompression for the column start_time with the TIMESTAMP data type using UDFs. For information on compression and decompression functions, see the compression and decompression functions in Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

CREATE TABLE BillDateTime
(item_ID INTEGER,
start_time TIMESTAMP(0)
COMPRESS USING TD_SYSFNLIB.ts_compress
DECOMPRESS USING TD_SYSFNLIB.ts_decompress);