15.00 - Rules and Restrictions for Adding Columns to a Column-Partitioned Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules and Restrictions for Adding Columns to a Column‑Partitioned Table

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

  • 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.

  • You cannot modify a column more than once in a single ALTER TABLE request.
  • You can only specify an INTO clause for an ADD clause of an ALTER TABLE request if the object to be altered is a column-partitioned table.
  • You cannot use an ALTER TABLE request 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 request.

  • 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.

  • If you add a column set to a column partition and that partition has system‑determined COLUMN or SYSTEM partition format, Teradata Database 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.
  • Generally, Teradata Database assigns COLUMN format to a narrow column partition and ROW format to a wide column partition.

    You can either submit a HELP COLUMN request or query an appropriate data dictionary view to determine the format that Teradata Database chose for a column partition.

  • If you add columns to a column partition and the partition has a user‑specified COLUMN, ROW, or SYSTEM format, Teradata Database does not redetermine the column partition format, and it does not change the format for the altered column partition.
  • When you add columns to a column partition, Teradata Database 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.
  • 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.
  • 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.
  • Teradata Database assigns the new column partition a column partition number that is not currently in use. If no column partition number is available, Teradata Database returns an error to the requestor.

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

  • If you do not specify an INTO clause but you add a group of one or more columns delimited by parentheses, the following things are true.
  • The object being altered must be a column‑partitioned table. This means that 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.
  • Teradata Database adds the group of columns as a new column partition consisting of those columns and assigns a new column partition number that is not being used to it.
  • If there are no column partition numbers available, Teradata Database aborts the request and returns a message to the requestor.

  • If you specify COLUMN format, Teradata Database 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, Teradata Database 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 Teradata Database.

    If you specify neither COLUMN nor ROW format explicitly or if you specify SYSTEM format explicitly, Teradata Database 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, Teradata Database 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.
  • Teradata Database bases its choice of default format 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, Teradata Database assigns a COLUMN format to narrow column partitions and a ROW format to wide column partitions.

    You can either submit a HELP COLUMN request 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 drop column partitions in the same ALTER TABLE request as one used to add column partitions, Teradata Database drops those column partitions before it adds any new column partitions.
  • If you add a column partition, the number of defined column partitions, which always includes 2 column partitions that Teradata Database employs 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.

    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.

    That means 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. Another way to say this is that the column partitions of a column‑partitioned table or join index can be all containers, all subrows, or a mix of both 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. Keep in mind that column partition numbers might not correspond to the order in which the column partitions were defined for an object.

    Whether you specify an INTO clause or not, Teradata Database always adds a column as the last column with respect to an ASTERISK (*) specification used to select all of the columns from the table.