Adding Columns to a Column-Partitioned Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The following rules and restrictions apply to adding new columns to a column-partitioned table.

The column cannot currently exist and the system adds it as a new column partition consisting of that single column.

The system adds a group of columns as a new column partition that consists of those columns.

KANJI1 Server Character Set Not Allowed

You cannot add columns with a server character set of KANJI1 to a column-partitioned table.

You should instead use the UNICODE server character set.

Single Modification to a Column Per ALTER TABLE statement

You cannot modify a column more than once in a single ALTER TABLE statement.

INTO Clause and Column-Partitioned Tables

You can only specify an INTO clause for an ADD clause of an ALTER TABLE statement if the object to be altered is a column-partitioned table.

If you do not specify an INTO option and a single column that is not delimited by parentheses is being added that does not currently exist for a column-partitioned table, the column is added as a new column partition consisting of that column. The system assigns a partition number to the new column partition that is not currently in use. The system determines the partition format for the new column partition and defines it with autocompression.

Whether or not you specify the INTO clause option, the system adds a new column as the last column, with respect to an * (ASTERISK character) that is used to select all columns from the table.

You cannot use an ALTER TABLE statement to modify the definition of a join index by adding columns, column partitions, or both.

An INTO clause cannot specify a column name that does not exist in the table.

You cannot add a new column and specify it in an INTO column in the same ALTER TABLE statement.

If you specify an INTO clause for a column or a group of columns, the column set being added becomes part of the column partition containing the column specified by column_name in the INTO clause.

You cannot add a column to a column partition that is already defined for that partition.

You cannot add a column to a column partition that is already defined in another column partition.

Column Partition Number

When you add columns to a column partition, Vantage assigns the altered column partition a new column partition number.

Adding columns to a column partition does not affect the number of defined column partitions (including the 2 internal use column partitions), the number of column partitions that can be added, the maximum number of column partitions, or the maximum column partition number.

Adding a Single-Column Partition Without Using INTO

If you do not specify an INTO clause and you add a single column that does not currently exist for a column-partitioned table, the column being added is added as a new column partition consisting of only that column.

Vantage assigns the new column partition a column partition number that is not currently in use. If no column partition number is available, the system returns an error to the requestor.

The format for the new column is system-determined and defined with autocompression by default.

You can specify either AUTO COMPRESS or NO AUTO COMPRESS for a new column partition added to a table if the specification is not preceded by INTO.

Adding a Group of Columns as a Partition Without Using INTO

If you do not specify an INTO clause but you add a group of one or more columns delimited by parentheses:
  • The object being altered must be a column-partitioned table. You cannot alter a column-partitioned join index to add columns or column partitions.
  • The columns in the group must not currently be defined for the table.
  • The group of columns is added as a new column partition consisting of those columns and assigns a new column partition number.

    If there are no column partition numbers available, the system returns a message to the requestor.

  • If you specify COLUMN format, Vantage stores one or more column partition values in a physical row called a container to compress row headers.

    If you specify a grouping with ROW format, Vantage stores only one column partition value is stored in a physical row as a subrow, and the row headers are not compressed. This is equivalent to the format that is normally used by Vantage.

    If you specify neither COLUMN nor ROW format explicitly or if you specify SYSTEM format explicitly, Vantage determines whether to use COLUMN or ROW format for the column partition.

    A column partition value consists of the values of the columns in the column partition for a specific table row.

  • If you specify either AUTO COMPRESS or NO AUTO COMPRESS, Vantage applies or does not apply the appropriate autocompression to the specified physical rows. However, it does apply any user-specified compression and, for column partitions with COLUMN format, row header compression, to the physical rows.

Default Format of Column Partitions

The choice of default format is based on the size of the column partition value for the added column partition and other factors such as whether a column partition value for the column partition has fixed or variable length and whether the column partition is a single-column or multicolumn partition.

As a general rule, COLUMN format is assigned to narrow column partitions and a ROW format to wide column partitions.

You can either submit a HELP COLUMN statement or query an appropriate data dictionary view to determine the format chosen for an added column partition. You can also specify an explicit format for a column partition.

If you add a column set to a column partition and that partition has system-determined COLUMN or SYSTEM partition format, Vantage redetermines the column partition format, basing its choice of format on the size of a column partition value for the altered set of columns in the column partition and other factors such as whether a column partition value for the partition has fixed or variable length.

If you add columns to a column partition and the partition has a user-specified COLUMN, ROW, or SYSTEM format, the system does not change the format for the altered column partition.

Dropping and Adding Column Partitions in the Same ALTER TABLE Statement

If you drop and add column partitions in the same ALTER TABLE statement, the system drops those column partitions before adding any new column partitions.

Number of Defined Column Partitions

If you add a column partition, the number of defined column partitions, which always includes 2 column partitions for internal use, is incremented by one and the number that can be added is decremented by 1.

The maximum number of column partitions and the maximum column partition are not affected.

Column Partition COLUMN, ROW, or SYSTEM Format

An individual column partition either has COLUMN, ROW, or SYSTEM format. It cannot have a mix of any formats. However, different column partitions of a column-partitioned object can have different formats.

The column partitions of a column-partitioned table can have all COLUMN format, all ROW format, all SYSTEM format, or a mix of COLUMN, ROW, or SYSTEM formats. The column partitions of a column-partitioned table or join index can be all containers, all subrows, or a mix of containers and subrows.

A column partition number cannot be less than 1 or greater than the maximum column partition number for the table or join index. Column partition numbers might not correspond to the order in which the column partitions were defined for an object.

Regardless of whether you specify an INTO clause, the system always adds a column as the last column for an ASTERISK (*) specification used to select all of the columns from the table.