17.10 - Adding, Modifying, or Dropping Algorithmic 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)
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 both 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 both 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.
  • 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 automatically 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 as well as the compression multivalue for the column if it specifies both 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.

  • For the rules that apply to writing UDFs to support algorithmic compression and decompression, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.
  • Teradata provides external UDFs for algorithmic compression and decompression. See Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.