15.00 - Adding Columns to a Column Partition in 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

Adding Columns to a Column Partition in a Column‑Partitioned Table

The following rules apply to adding columns to a column partition in a column‑partitioned table.

  • A column partition number cannot be less than 1 or exceed the maximum column partition number of the table.
  • Column partition numbers might not correspond to the order that the column partitions were defined.

  • While you can use both row and column partitioning for the same table, you cannot mix both formats in the same partitioning level. A column partition either has COLUMN format or it has ROW format.
  • If a column to be added is already defined for that column partition, Teradata Database aborts the request and returns an error to the requestor.
  • If a column to be added is already defined in another column partition, Teradata Database aborts the request and returns an error to the requestor.
  • If you add columns to a column partition that has user‑specified COLUMN, ROW, or SYSTEM format, the format is retained for the altered column partition.
  • If you add columns to a column partition, the altered column partition is assigned to a new column partition number.
  • Adding columns to a column partition does not affect the number of defined column partitions, which includes 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.
  • Teradata Database bases its choice of system‑determined COLUMN or ROW format for an added column partition 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.
  • Teradata Database generally determines a narrow column partition (defined as 256 or fewer bytes) to have COLUMN format and a wide column partition to have ROW format.

    You can use HELP COLUMN requests or retrieve the appropriate rows using a data dictionary view to determine the system‑column partition form that Teradata Database chose for a column partition. See Data Dictionary for lists of the views provided by Teradata.

  • If you both add and drop column partitions in the same ALTER TABLE request, Teradata Database drops the specified column partitions before it adds any new column partitions.
  • If you add a new column partition to a table, Teradata Database increments the number of defined column partitions, including the 2 internal use column partitions, by 1, and decrements the number of partitions that can be added by 1.
  • The maximum number of column partitions and the maximum column partition for the table are not affected by adding a new column partition.