15.10 - COMPRESS USING - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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)/ REPLACE FUNCTION (External Form)” in SQL Data Definition Language - Detailed Topics, B035-1184 and 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 Teradata Database 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 SQL Fundamentals, B035-1141.

For more information about algorithmic compression, see SQL Data Definition Language - Detailed Topics, B035-1184 and 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 the following data types:
  • ARRAY/VARRAY
  • BLOB, BLOB-related UDT
  • BYTE
  • CHARACTER
  • CLOB, CLOB-related UDT
  • Geospatial
  • GRAPHIC
  • TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE
  • Distinct UDTs
  • XML
Structured UDTs cannot be algorithmically compressed.
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 “Compression/Decompression Functions” in SQL Functions, Operators, Expressions, and Predicates.

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