15.00 - Dropping Columns from a Column-Partitioned Table - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Dropping Columns from a Column‑Partitioned Table

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.
  • Column partition numbers might not correspond to the order that the column partitions were defined.

  • If you specify to drop all of the columns of a column‑partitioned table other than the 2 internal use partitions, Teradata Database aborts the request and returns an error to the requestor.
  • You cannot drop the 2 internal use column partitions from a table.
  • If you specify to drop columns from a column partition and there are no other columns in the altered column partition, the following things occur.
  • Teradata Database drops the column partition.
  • The column partition number from the dropped column partition becomes available to be used for adding another column partition.
  • When this happens, Teradata Database 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, Teradata Database drops the specified column partitions before adding the new column partitions.
  • You can drop all the existing columns from a column partition, but Teradata Database does not drop the partition if you also add new columns to the column partition in the same ALTER TABLE request.
  • If you specify to drop columns from a column partition and there are other columns in the altered column partition, the following things occur.
  • If you drop a column set from a column partition and that column partition has system‑determined column partition format, Teradata Database 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.
  • 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 the column partition from which the column set was dropped has user‑specified COLUMN, ROW, or SYSTEM format, Teradata Database does not change the format for the altered column partition.
  • Teradata Database 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.