Using Compression Methods with Table Columns - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Adding, Modifying, or Dropping Block-Level Compression

You cannot use an ALTER TABLE statement to add, modify, or drop the block-level compression characteristics of a table or join index. For a description of the restrictions on specifying block-level compression for volatile and global temporary tables, see Block-Level Compression and Tables.
For Object File System tables, BLOCKCOMPRESSION has a default value that you cannot change.

Adding, Modifying, or Dropping Algorithmic Compression

The following rules apply to using an ALTER TABLE request to add, modify, or drop algorithmic compression for a column.
  • You can use an ALTER TABLE request to add a new table column that has only algorithmic compression or that has value and algorithmic compression.
  • You can use an ALTER TABLE request to modify the algorithmic compression characteristics of an existing column only if the table contains no data.
  • You cannot use an ALTER TABLE request to add algorithmic compression for a column that has a structured UDT data type, including a column that use embedded services UDFs for compression.
  • When you specify a UDF name for algorithmic compression, you must also specify its containing database or user.
  • You cannot specify algorithmic compression for a VALIDTIME or TRANSACTIONTIME column that has a Period data type.
  • If a table is populated with data, you cannot modify its column compression attributes if either the current or planned compression attribute specifies algorithmic compression.

    The following table summarizes the supported compression modification cases.

    Current Compression Modified Compression
    None Multivalue Only Algorithmic Only Multivalue and Algorithmic
    None yes yes no no
    Multivalue Only yes yes no no
    Algorithmic Only no no no no
    Multivalue and Algorithmic no no no no
  • You must specify a compression algorithm and a decompression algorithm for any column specified to have algorithmic compression.

    You can specify multivalue compression and algorithmic compression for a column in any order.

  • When you specify multivalue compression and algorithmic compression for the same column, Vantage applies algorithmic compression only to values that are not specified with multivalue compression.
  • If you specify only algorithmic compression for a column, there is no limit on the size of the specified data type.
  • If you specify algorithmic and multivalue compression for a column, the size of the data type is restricted to the same data type limitations as those for multivalue compression.
  • Support for algorithmic compression is restricted to the following data types.
    • BLOB
    • BYTE
    • CHARACTER

      Vantage implements the GRAPHIC data type as CHARACTER CHARACTER SET GRAPHIC.

    • CLOB
    • Geospatial
    • VARBYTE
    • VARCHAR
    • VARGRAPHIC
    • JSON
    • XML
    • Period
    • All distinct BLOB-based, CLOB-based, and XML-based UDT types

      You can also compress nulls for data in the following types of columns.

    • Distinct UDT
    • ARRAY/VARRAY
    • Period types, but not derived Period types
  • You cannot specify algorithmic compression for a column that is a component of the primary index for a table.
  • You can specify algorithmic compression for a column that is a component of a secondary index for a table.
  • You cannot specify algorithmic compression for a column in a standard referential integrity relationship.
    Referential integrity is supported only on the Block File System on the primary cluster, not on the Object File System.
  • You can specify algorithmic compression for a column in a Batch referential integrity relationship and for a column that is a component of a Referential Constraint.
  • You can specify algorithmic compression for columns in a permanent base table and for columns in a global temporary table.
  • Vantage compresses nulls in a column that specifies algorithmic compression.
  • The dictionary table column DBC.TVFields.CompressValueList contains the names of the algorithmic compression and algorithmic decompression UDFs specified for a column and the compression multivalue for the column if the column specifies algorithmic and multivalue compression.

    If the size of CompressValueList for a table column exceeds 8,192 characters, the system returns an error to the requestor.

  • Teradata provides external UDFs for algorithmic compression and decompression. See Compression/Decompression Functions.

Adding or Modifying Multivalue Compression for a Column

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 compressed, even if not explicitly specified.
  • You can compress 0 columns.
  • You cannot compress 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.
  • You can modify 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.
    Procedure for first workaround:
    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 to be replaced.
    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.
    Procedure for second workaround:
    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 INSERT … SELECT.
    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.
    Condition NO COMPRESS Effect
    Specified column exists. Drops the compression from the specified column.
    Specified column does not exist. Makes 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. 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 the column cannot be compressed.
    • Base table columns on which a join index is defined.

      If you do this, the system returns a warning message advising you that you must recreate the join index to reflect the compression changes.

    • 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: Store1, Store2, Store3.

      The following ALTER TABLE request adds Store5 to the list of compressed values for column_3.

      ALTER TABLE table_ex
  ADD column_3
  COMPRESS ('Store1','Store2','Store3','Store4','Store5');
    • 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: Store1, Store2, Store3, Store4, Store5.

      The following ALTER TABLE request drops Store5 from the list of compressed values for column_3.

      ALTER TABLE table_ex
  ADD column_3
  COMPRESS ('Store1','Store2','Store3','Store4');
  • 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 is trying 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 is trying to add the string ‘Store1’ 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.

    • Adding multivalue compression to a column of one the following data types: LONG VARCHAR, BLOB, CLOB, UDT with any underlying data type, structured UDT with any underlying data type set

      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.