17.10 - Modifying Column Data Types or Multivalue Compression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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
  • there is no default and all values must be specified explicitly; however, if a column is nullable, then null compression is in effect even if it is not specified explicitly.
  • you can specify the values you want to compress in any order for a column.
  • you can only specify a value once in a multivalue compression list for a column.

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.

Multivalue compression is not supported for columns with the following data types:
  • 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.