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.
- BYTE
- VARBYTE
- DATE
- CHARACTER
- VARCHAR
- GRAPHIC
- VARGRAPHIC
- TIME
- TIME WITH TIME ZONE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- All numeric types
- DATE
- CHARACTER and GRAPHIC
- VARCHAR and VARGRAPHIC
- BYTE
- VARBYTE
- BLOB
- CLOB
- ARRAY/VARRAY
- Structured UDTs
- Period
- XML
- Geospatial
- JSON
- DATASET
- 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.
- NULL
- Nulls are compressed for the column.
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).