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.