You can compress nulls and as many as 255 distinct values per column. You can compress an unlimited number of columns per table. This value is constrained by the maximum system row length because compressed values are added to the table header row for each column.
| Type of Multivalue Compression | Description |
|---|---|
| Single-valued | Default is null if no value is specified explicitly. |
| Multivalued |
|
You can add a new column with multivalued compression to an existing table, add multivalued compression to an existing column, or you can drop compression from an existing column by specifying the NO COMPRESS attribute.
If a column is constrained as NOT NULL, then none of the specifications in the compression list can be the literal NULL.
Columns defined with the COMPRESS attribute cannot participate in fast path INSERT … SELECT operations, so if you execute an INSERT ... SELECT request on a target table that has multivalue compressed columns, the Optimizer does not specify fast path optimization for the access plan it creates.
Usually, the performance cost of not being able to take advantage of the fast path INSERT ... SELECT is more than offset by the performance advantages of multivalue compression.
- Identity
- LONG VARCHAR
- BLOB
- CLOB
If the data type of any column in the new table is not compatible with the value of the corresponding field in the existing table, individual INSERT requests must be used to load each row.
If all the new data types are compatible with all the existing values (for example, only the COMPRESS attribute is being changed), you can use an INSERT … SELECT request to copy all the rows in a single request. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Also see Rules for Adding or Modifying Multivalue Compression for a Column.
See Teradata Vantage™ - Database Design, B035-1094 for more detailed information about multivalue compression and its applications for performance optimization and disk capacity savings.