15.00 - Rules for Adding or Modifying Multi-Value Compression for a Column - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Rules for Adding or Modifying Multi-Value Compression for a Column

The following rules apply to adding or modifying rows with multi-value compression.

  • For single‑valued compression, the default for the compress value is NULL.
  • For multi-valued compression, there is no default compress value.
  • All values in the multi-value must be specified explicitly.

  • Nulls in a nullable column are always compressed automatically, even if not explicitly specified.
  • You can compress as few as 0 or as many as all but the primary index column set of a table. Neither can you compress partitioning column values when the primary index or table is row‑partitioned.
  • The maximum number of distinct values that can be compressed per column is 255.
  • If an attempt to modify or add the definition for a new column results in the table row header exceeding the maximum row length for the Teradata Database, then the responsible ALTER TABLE request fails.
  • You can modify an existing column to have multi-valued compression.
  • In addition to the direct method of modifying an existing column to use multi-valued compression, there are two workarounds for cases where you want to convert an existing column to support multi-valued compression.
  • The procedure for the first workaround is as follows.
  • i Use an ALTER TABLE request to add a new column with the desired multi-valued compression.

    i Use an ALTER TABLE request to add a new column with the desired multi-valued compression.

    ii Populate the new column by copying the data from the column it is designed to replace.

    iii Use ALTER TABLE to drop the old column.

    iv Use ALTER TABLE to rename the new column to match the name of the dropped column.

  • The procedure for the second workaround is as follows.
  • i Use an ALTER TABLE request with the desired multi-valued compression defined where needed.

    i Use an ALTER TABLE request with the desired multi-valued compression defined where needed.

    ii Populate the new table by copying the data from the old table using an INSERT … SELECT request.

    iii Use a DROP TABLE request to drop the old table.

    iv Use an ALTER TABLE request to rename the new table to match the name of the dropped table.

  • You can drop compression from an existing column or make values uncompressible for a new column by specifying the NO COMPRESS option.
  •  

    IF the specified column …

    THEN you can use NO COMPRESS to …

    already exists

    drop the compression from the specified column.

    does not already exist

    make the column values not compressible for the specified column.

  • The effect of adding the COMPRESS attribute to an existing column depends on the specific definition of the COMPRESS clause, as is explained by the following cases. Note that you cannot specify null compression if the specified column is defined with the NOT NULL attribute.
  • In the following case, the system compresses nulls only for the specified column.
  •      ALTER TABLE table_name 
           ADD column_name COMPRESS;

    This is equivalent to the following request.

         ALTER TABLE table_name 
           ADD column_name COMPRESS NULL;
  • In the following case, the system compresses nulls and the specified constant value for the specified column.
  •      ALTER TABLE table_name 
           ADD column_name COMPRESS <constant | NULL>;

    If column_name already exists, and a compression multi-value set is already defined for it, then the newly specified compression multi-value set replaces the existing set.

    Note that the expression <constant | NULL> is not SQL, but is symbolic of a specification of either a constant value (meaning that both that constant and nulls are to be compressed for the specified column) or only the keyword NULL.

  • In the following case, the system compresses nulls and the specified list of constant values for the specified column.
  •      ALTER TABLE table_name 
           ADD column_name COMPRESS (<constant_list | NULL>);

    If column_name already exists, and a compression multi-value set is already defined for it, then the newly specified compression multi-value set replaces the existing set.

    Note that the expression <constant_list | NULL> is not SQL, but is symbolic of a specification of either a list of constant values (meaning that both that list of constants and nulls are to be compressed for the specified column) or only the keyword NULL.

  • Specifying NO COMPRESS when adding a new column has the same effect as not specifying COMPRESS for that column. The column cannot be defined with multi-value compression within the same request that adds the new column.
  • Specifying NO COMPRESS when modifying an existing column drops the COMPRESS attribute from that column.
  • The ALTER TABLE ADD COMPRESS syntax supports the following tables and columns.
  • Both loaded and empty tables of all types.
  • Global Temporary tables.
  • Secondary index columns unless the column is the PRIMARY KEY or FOREIGN KEY in a referential constraint, in which case it cannot be compressed.
  • Base table columns on which a join index is defined.
  • If you do this, Teradata Database returns a warning message advising you that you must recreate the join index to reflect the compression changes.

  • Base table columns on which a hash index is defined.
  • Modification of multiple columns in a single ALTER TABLE request. For example, the following ALTER TABLE request is valid.
  •      ALTER TABLE table_ex
           ADD column_1 COMPRESS (0,100, 200),
           ADD column_2 COMPRESS ('Female','Male'),
           ADD column_3 COMPRESS ('Teradata');
  • Specification of changes to multiple options within the same ALTER TABLE request. For example, the following ALTER TABLE request is valid.
  •      ALTER TABLE table_ex
           ADD column_1 COMPRESS (0,100, 200),
           DROP column_2,
           ADD column_3 COMPRESS ('Teradata'),
           ADD column_4 INTEGER;
  • Changing an uncompressed column to have multi-value compression. For example,
  •      ALTER TABLE table_ex
           ADD column_4 COMPRESS (0, 1000, 10000);
  • Changing a compressed column to not being compressed. For example,
  •      ALTER TABLE table_ex
           ADD column_4 NO COMPRESS;
  • Adding compressed values to an existing multi-value compression. This can only be done by replacing the existing compression multi-value for a column. For example, suppose column_3 in table_ex already compresses the following set of values: WalMart, JCPenney, Kmart.
  • The following ALTER TABLE request adds Harrahs to the list of compressed values for column_3.

         ALTER TABLE table_ex
           ADD column_3 
           COMPRESS ('WalMart','JCPenney','Kmart','Sears','Harrahs');
  • Dropping compressed values from an existing compression multi-value list. This can only be done by replacing the existing compression multi-value list for a column. For example, suppose column_3 in table_ex already compresses the following set of values: WalMart, JCPenney, Kmart, Sears, Harrahs.
  • The following ALTER TABLE request drops Harrahs from the list of compressed values for column_3.

         ALTER TABLE table_ex
           ADD column_3
           COMPRESS ('WalMart','JCPenney','Kmart','Sears');
  • The ALTER TABLE MODIFY COMPRESS syntax does not support the following features.
  • A compression multi-value list that exceeds the maximum size of 8,192 characters as defined by the column DBC.TVField.CompressValueList.
  • A compression multi-value list that causes the table header to exceed its maximum size of 128 kilobytes.
  • A compression multi-value list that causes the Transient Journal to exceed its maximum size of 65,535 bytes.
  • A compression multi-value list with values that are incompatible with the specified data type for the column. An example of this would be attempting to add a character string to a multi-value compression for a column typed as INTEGER.
  • A compression multi-value that exceeds the maximum number of 255 unique values for a column.
  • NULL compression when the column has a NOT NULL attribute.
  • A compression value or multi-value that contains a character that is not in the character set for the current session.
  • A compression value that duplicates a value that is already in the compression multi-value. An example of this would be attempting to add the string ‘WalMart’ twice to a compression multi-value for a CHARACTER column.
  • When this occurs, the request aborts and the system returns an error to the requestor.

  • Modifying the compression multi-value characteristics of the same column more than once in the same ALTER TABLE request.
  • When this occurs, the request aborts and the system returns an error to the requestor.

  • Modifying both the multi-value compression characteristics and the characteristics of a constraint in the same ALTER TABLE request.
  • When this occurs, the request aborts and the system returns an error to the requestor.

  • Modifying a primary index column, whether a unpartitioned or a partitioned, to add multi-value compression because the columns of a primary index cannot be value‑compressed.
  • When this occurs, the system returns an error to the requestor.

  • Adding multi-value compression to a column that is a member of the partitioning column set for a row‑partitioned table.
  • When this occurs, the system returns an error to the requestor.

  • Modifying any column that is part of a referential integrity constraint to add multi-value compression.
  • When this occurs, the request aborts and the system returns an error to the requestor.

  • Modifying an identity column to add multi-value compression because you cannot value compress identity columns.
  • When this occurs, the request aborts and the system returns an error to the requestor.

  • Modifying a column that has any of the following data types to add multi-value compression.
  • LONG VARCHAR
  • BLOB
  • CLOB
  • A UDT, no matter what its underlying data type (or, if a structured UDT, data type set) contains.
  • When this occurs, the request aborts and the system returns an error to the requestor.

  • The expanded compress values for a column are stored in the data dictionary.
  • If the text exceeds the 8,192 character maximum size for DBC.TVFields.CompressValueList, then the responsible ALTER TABLE request fails.

  • The list of compressed values for a nullable column can be null or one or more distinct constant values.
  • The list of compressed values for a non-nullable column cannot include nulls.
  • You cannot specify the same distinct value more than once in a compressed multi-value.