ALTER TABLE Syntax Elements (Join Index) - 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
database_name
The name of the containing database. The default is the database for the current session.
user_name
The name of the containing user. The default is the database for the current user.
join_index
The name of the join index to be modified.

ADD COLUMN (column_name)

The column partition containing the column uses COLUMN format.

You can include columns in the join index with a data type of non-LOB XML, non-LOB ST_GEOMETRY, non-LOB JSON, and non-LOB DATASET. However, you cannot include these data types in the primary index of a join index.

A column in a join index cannot have a data type of BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, VARIANT_TYPE, ARRAY, VARRAY, LOB XML, LOB ST_GEOMETRY, LOB JSON, or DATASET LOB.

AUTO COMPRESS
The column partition containing the column use COLUMN format with autocompression.
NO AUTO COMPRESS
The column partition containing the column use COLUMN format without autocompression.

ADD ROW (column_name)

The column partition containing the column use ROW format.

AUTO COMPRESS
The column partition containing the column use ROW format with autocompression.
NO AUTO COMPRESS
The column partition containing the column use ROW format without autocompression.

ADD SYSTEM (column_name)

Alters the column partition containing the column to have a system-determined format of COLUMN or ROW.


AUTO COMPRESS
Alters the column partition containing the column to have a system-determined format of COLUMN or ROW and adds autocompression.

NO AUTO COMPRESS
NO AUTO COMPRESS
 alters the column partition containing the column to have a system-determined format of COLUMN or ROW and removes autocompression.

ADD (column_name) AUTO COMPRESS

Add autocompression for the column partition. You can add compression to a set of existing columns whether the table is empty or contains data. You can only specify autocompression for column-partitioned tables. 

You cannot modify the autocompression or partition storage for a column partition more than once in the same ALTER TABLE request.


AUTO COMPRESS
Applies autocompression to the column or column partition. AUTO COMPRESS is the default. Vantage applies autocompression for a physical row on a per container basis. For efficiency, the system may use the autocompression method chosen for the previous container, including not using autocompression, if that is more effective.
NO AUTO COMPRESS
The column partition containing the column is not autocompressed.