The following rules and restrictions apply to dropping columns from a column-partitioned table.
- 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 that the column partitions were defined.
- You cannot drop all of the columns of a column-partitioned table other than the 2 internal use partitions, the system returns an error to the requestor.
- You cannot drop the 2 internal use column partitions from a table.
- If you drop all of the columns from a column partition, the following things occur.
- The column partition is dropped.
- The column partition number from the dropped column partition becomes available for adding another column partition.
The system decrements the number of defined column partitions by 1 and increments the number of column partitions that can be added by 1.
The maximum number of column partitions and the maximum column partition number for the table are unaffected.
- If you add multiple column partitions are in the same ALTER TABLE request that deletes column partitions, the system drops the specified column partitions before adding the new column partitions.
- You can drop all the existing columns from a column partition, but the system does not drop the partition if you also add new columns to the column partition in the same ALTER TABLE request.
- If you drop columns from a column partition and there are other columns in the altered column partition:
- If you drop a column set from a column partition and that column partition has system-determined column partition format, the system redetermines the column partition format based on the size of a column partition value for the remaining set of columns in the column partition and other factors such as whether a column partition value for the column partition has fixed or variable length.
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 the column partition from which the column set was dropped has user-specified COLUMN, ROW, or SYSTEM format, Vantage does not change the format for the altered column partition.
- Vantage assigns a different column partition number to the altered column partition.
- 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, and the maximum column partition number are not affected by these operations.
- If you drop a column set from a column partition and that column partition has system-determined column partition format, the system redetermines the column partition format based on the size of a column partition value for the remaining set of columns in the column partition and other factors such as whether a column partition value for the column partition has fixed or variable length.