Examples: Compression Attribute - Teradata Vantage

Example: Specifying Multivalue Compression

    CREATE TABLE employee (
      emp_no   INTEGER       NOT NULL,
      emp_name CHARACTER(30) NOT NULL COMPRESS ('Smith', 'Wong',
                                                'Rodriguez')
       ...
      dob      DATE          COMPRESS (NULL, DATE '1972-02-29',
                                             DATE '1976-02-29')
       ...);

Example: Discrepancy between Explicit Column Data Type and Implicit Data Type of Multivalue Compression

This example demonstrates the problem with specifying compressed numeric data values in an implicit data type that differs from the explicit type specified for the column containing the value to be compressed. The problem is restricted to bidirectional conversions between the DECIMAL/NUMERIC data type and the REAL/FLOAT/DOUBLE PRECISION data type.

This example shows the case for a column defined with an explicit FLOAT data type, but a compression value specified with an implicit DECIMAL data type.

     CREATE TABLE comptest, NO FALLBACK (
       col_1 INTEGER NOT NULL,
       col_2 FLOAT COMPRESS 0.58)
     PRIMARY INDEX (col_1);

This example shows the case for a column defined with an explicit DECIMAL data type, but a compression value specified with an implicit FLOAT data type:

     CREATE TABLE comptest3, NO FALLBACK (
       col_1 INTEGER NOT NULL,
       col_2 DECIMAL(3,2) COMPRESS 0.07E0 )
     PRIMARY INDEX (col_1);

This example returns an error because the data type of the column (DECIMAL) differs from the implicit data type of the compression value specified (FLOAT).

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 more information, see Compression/Decompression Functions.

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