17.10 - COMPRESS constant - 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)

Multivalue compression for a set of distinct values in a column.

Using COMPRESS can save space, depending on the percentage of rows for which the compressed value is assigned.

You can specify multivalue compression for all numeric types, and the following predefined data types:
  • BYTE
  • VARBYTE
  • DATE
  • CHARACTER
  • VARCHAR
  • GRAPHIC
  • VARGRAPHIC
  • TIME
  • TIME WITH TIME ZONE
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
You can specify multivalue compression for columns defined with distinct UDT types based on the following predefined data types:
  • All numeric types
  • DATE
  • CHARACTER and GRAPHIC
  • VARCHAR and VARGRAPHIC
  • BYTE
  • VARBYTE
Multivalue compression is not supported for columns defined with the following data types:
  • BLOB
  • CLOB
  • ARRAY/VARRAY
  • Structured UDTs
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET
Multivalue compression is not supported for:
  • Any column that is a member of the primary index column set for a table.
  • Row-level security constraint columns.
  • Partitioning columns.

Source and target tables with the same compress attributes can use fast path INSERT ... SELECT. Columns defined with non-matching COMPRESS attributes cannot participate in fast path INSERT ... SELECT operations. If you perform an INSERT ... SELECT on a target table containing compressed columns defined with COMPRESS attributes that do not match, the Optimizer does not specify fast path optimization for the access plan it creates.

Usually, the performance advantages of multivalue compression offset the cost of not being able to use fast path INSERT ... SELECT.

See ALTER TABLE.

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

For a detailed description of multivalue compression, see Teradata Vantage™ - Database Design, B035-1094.

For more information about the COMPRESS attribute, see Teradata Vantage™ - Data Types and Literals, B035-1143.

constant
The specified value or list of values are compressed.
For information about limits for this value, see Teradata Vantage™ - Data Types and Literals, B035-1143.
NULL
Nulls are compressed for the column.
The following rules apply:
  • You cannot specify NULL if the column is defined as NOT NULL.
  • LOB-based UDT nulls cannot be compressed.
  • You can only specify NULL once per column.
If a column is constrained as NOT NULL, then none of the specifications in the multivalue compression list can be the literal NULL.

Example: Specifying Multivalue Compression

The following example compresses all emp_name values of Smith, Wong, and Rodriguez and all DOB values of 1972-02-29, 1976-02-29:
    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).