Rules for Adding or Modifying Multivalue Compression for a Column - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The following rules apply to adding or modifying rows with multivalue compression.

  • For single-valued compression, the default for the compress value is NULL.
  • For multivalued compression, there is no default compress value.

    All values in the multivalue 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. You cannot 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.
  • You cannot modify or add the definition for a new column results in the table row header exceeding the maximum row length.
  • You can modify an existing column to have multivalued compression.
  • In addition to the direct method of modifying an existing column to use multivalued compression, there are two workarounds for cases where you want to convert an existing column to support multivalued compression.
    • The procedure for the first workaround is as follows.
      1. Use an ALTER TABLE request to add a new column with the desired multivalued compression.
      2. Populate the new column by copying the data from the column it is designed to replace.
      3. Use ALTER TABLE to drop the old column.
      4. 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.
      1. Use an ALTER TABLE request with the desired multivalued compression defined where needed.
      2. Populate the new table by copying the data from the old table using an INSERT … SELECT request.
      3. Use a DROP TABLE request to drop the old table.
      4. 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.
    • To compress 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;
    • To compress nulls and a specified constant value for the specified column.
           ALTER TABLE table_name
             ADD column_name COMPRESS my_constant;

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

    • To compress nulls and the specified list of constant values for the specified column.
           ALTER TABLE table_name
             ADD column_name COMPRESS (constant_list);

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

  • 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 multivalue 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.
    • 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 noncompressed column to have multivalue 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 multivalue compression. This can only be done by replacing the existing compression multivalue 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 multivalue list. This can only be done by replacing the existing compression multivalue 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 multivalue list that exceeds the maximum size of 8,192 characters as defined by the column DBC.TVField.CompressValueList.
    • A compression multivalue list that causes the table header to exceed its maximum size of 128 kilobytes.
    • A compression multivalue list that causes the Transient Journal to exceed its maximum size of 65,535 bytes.
    • A compression multivalue 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 multivalue compression for a column typed as INTEGER.
    • A compression multivalue 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 multivalue 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 multivalue. An example of this would be attempting to add the string ‘WalMart’ twice to a compression multivalue for a CHARACTER column.

      When this occurs, the request aborts and the system returns an error to the requestor.

    • Modifying the compression multivalue 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 multivalue 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 nonpartitioned or partitioned, to add multivalue compression because the columns of a primary index cannot be value-compressed.

      When this occurs, the system returns an error to the requestor.

    • Adding multivalue 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 multivalue compression.

      When this occurs, the request aborts and the system returns an error to the requestor.

    • Modifying an identity column to add multivalue 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 multivalue 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 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 multivalue.