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, the system returns an error to the requestor.
- If a column to be added is already defined in another column partition, the system 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.
- Vantage 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.
Vantage 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 Vantage chose for a column partition. See Teradata Vantage™ - Data Dictionary, B035-1092 for lists of the views provided by Teradata.
- If you both add and drop column partitions in the same ALTER TABLE request, Vantage drops the specified column partitions before it adds any new column partitions.
- If you add a new column partition to a table, Vantage 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.