15.00 - Modifying Column Data Types or Multi-Value Compression - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Modifying Column Data Types or Multi‑Value Compression

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.

 

IF you specify this type of multi‑value compression …

THEN …

single‑valued

the default is null if no value is specified explicitly.

multi‑valued

  • 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.
  • there is no essential ordering for specifying values to be compressed for a given column.
  • the same value cannot be specified more than once in the compression multi‑value list for a given column.
  • You can add a new column with multi-valued compression to an existing table, add multi-valued 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 multi-value 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 multi-value compression.

    Multi-value compression is not supported for the following types of columns.

  • 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 SQL Data Manipulation Language).

    Also see “Rules for Adding or Modifying Multi-Value Compression for a Column” on page 63.

    See Database Design for more detailed information about multi-value compression and its applications for performance optimization and disk capacity savings.