15.00 - 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

Adding Columns to a Column‑Partitioned Table

You can either add a new column to a table or modify the definition of an existing column using the ADD option.

The following rules and restrictions apply to adding new columns to a column‑partitioned table when you do not specify an INTO clause for an ADD operation of a single column definition that is not delimited by parentheses.

  • The column cannot currently exist and Teradata Database adds it as a new column partition consisting of that single column.
  • You cannot add a column that is specified with the server character set of CHARACTER SET KANJI1 to a column‑partitioned table.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

    You should instead specify a server character set of CHARACTER SET UNICODE.

  • Teradata Database adds a group of columns as a new column partition that consists of those columns.
  • The following rules apply to specifying the INTO column_name option when adding a new column to a column‑partitioned table.
  • Whether you specify the INTO clause option or not, Teradata Database adds a new column as the last column with respect to an * (ASTERISK character) that is used to select all columns from the table.
  • You can only specify the INTO option for an ADD operation if the specified object to be altered is a column-partitioned table,
  • If you attempt to specify INTO for an ADD clause in a table that is not column‑partitioned, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify the INTO option, but the column specified by the column name does not exist for the table, Teradata Database aborts the request and returns an error to the requestor.
  • This means that you cannot add a new column to a table and also specify the INTO option for that column in the same ALTER TABLE request.

  • If you specify the INTO option for a column set being added to a table, the column set being added is added to the column partition containing the column specified by the column name you specify in the INTO clause.
  • If you do not specify an INTO option and a single column that is not delimited by parentheses is being added that does not currently exist for a column‑partitioned table, the column is added as a new column partition consisting of that column.
  • Teradata Database assigns a partition number to the new column partition that is not currently in use.

    If there are no available column partition numbers, Teradata Database aborts the request and returns an error to the requestor.

    Teradata Database determines the partition format for the new column partition and defines it with autocompression.

  • If you do not specify an INTO option and you add a set of parenthetically delimited columns to a table, the following rules apply.
  • The object being altered must be a column‑partitioned table.
  • If it is not, Teradata Database aborts the request and returns an error to the requestor.

  • The columns in the set must not currently exist in the table.
  • If any of the columns already exist in the table, Teradata Database aborts the request and returns an error to the requestor.

  • Teradata Database adds the set of columns as a new column partition containing those columns and assigns a column partition number to the partition that currently is not in use.
  • If there are no available column partition numbers, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify COLUMN format, Teradata Database stores the set of column partition values in a physical row referred to as a container row using COLUMN format.
  • If you specify ROW format for a column set, Teradata Database stores only one column partition value in a physical row as a subrow using ROW format.

    If you do not specify either format explicitly, or if you specify SYSTEM format, Teradata Database determines whether to use COLUMN or ROW format for the column partition.

    A column partition value is the set of column values in a column partition for a specific table row.

  • The partitions of a column-partitioned table can have all COLUMN format, all ROW format, all SYSTEM format, or a mix of all formats.
  • An individual column either has COLUMN format, ROW format, or SYSTEM format. It cannot have a mix of formats.

    However, different column partitions of a column‑partitioned table or join index can have different formats. Fox example, the column partitions of a column‑partitioned table can have all COLUMN format, all ROW format, all SYSTEM format, or some column partitions can have COLUMN format and other column partitions in the same table or join index can have ROW format or SYSTEM format.

  • If you ADD a new column without also specifying an autocompression option, Teradata Database determines whether it has autocompression or not based on the value of the AutoCompressDefault cost profile constant, as the following table documents.
  •  

    IF AutoCompressDefault is set to this value …

    THEN a new table column that does not explicitly define autocompression using the AUTO COMPRESS or NO AUTO COMPRESS option …

    1

    has autocompression.

    2

    does not have autocompression.

  • The following table presents the rules for explicitly specified AUTO COMPRESS options.
  •  

    IF you specify …

    THEN Teradata Database …

    AUTO COMPRESS for a column

    applies autocompression for physical rows if it finds a compression method that reduces the size of a partition.

    If Teradata Database cannot find a compression method that reduces the size of a partition, it does not apply a compression method.

    NO AUTO COMPRESS for a column in the column list for the table

    does not apply autocompression for physical rows.

    In this case, Teradata Database does apply any user‑specified compression and, for column partitions with COLUMN format, row header compression, for the column partition.

  • If you specify COLUMN, the new column partition has COLUMN format.
  • If you specify ROW, the new column partition has ROW format.
  • If you specify SYSTEM, Teradata Database determines whether to assign COLUMN or ROW format based on the efficiency of the storage format for the column.
  • If you do not specify either COLUMN or ROW, Teradata Database determines whether to assign COLUMN or ROW format based on the efficiency of the storage format for the column.
  • Teradata Database redetermines the column partition format based 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 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.

    The following rules and restrictions apply to altering existing columns in a column‑partitioned table when you do specify a single column name that is delimited by parentheses for an ADD operation.

  • The column must already exist in the table or join index.
  • If you specify neither AUTO COMPRESS or NO AUTO COMPRESS for the ADD operation, you cannot have altered the autocompression of the column partition in which the column is contained in a preceding ADD operation in the same ALTER TABLE request.
  • If you specify COLUMN, ROW, or SYSTEM for the ADD operation, you cannot have altered the column partition format for the column partition in which the column is contained in a preceding ADD operation in the same ALTER TABLE request.
  • The rules in the following table define the behavior of an ADD operation that specifies one of the autocompression options.
  •  

    IF you specify this autocompression option for the ADD operation …

    THEN Teradata Database alters the column partition that contains the column …

    AUTO COMPRESS

    to have autocompression for physical rows if it finds a compression method that reduces the size of a partition, if that compression method is not already defined.

    If Teradata Database cannot find a compression method that reduces the size of a partition, it does not apply a compression method.

    NO AUTO COMPRESS

    not to have autocompression if that is not already defined.

    In this case, Teradata Database does apply any user‑specified compression and, for column partitions with COLUMN format, row header compression, for the column partition.

     

    IF you specify …

    THEN Teradata Database …

    AUTO COMPRESS for a column

    applies autocompression for physical rows if it finds a compression method that reduces the size of a partition.

    If Teradata Database cannot find a compression method that reduces the size of a partition, it does not apply a compression method.

    NO AUTO COMPRESS for an individual column in the column list for the table

    does not apply autocompression for physical rows.

    In this case, Teradata Database does apply any user‑specified compression and, for column partitions with COLUMN format, row header compression, for the column partition.

  • If you specify neither AUTO COMPRESS nor NO AUTO COMPRESS for an ADD operation, Teradata Database does not modify the autocompression of the column partition that contains the column.
  • The rules in the following table define the behavior of an ADD operation that specifies a column format.
  •  

    IF you specify the following column format for the ADD operation …

    THEN Teradata Database alters the storage format of the partition to have this format …

    COLUMN

    COLUMN if that is not already defined.

    ROW

    ROW if that is not already defined.

    SYSTEM

    SYSTEM if that is not already defined.

    none

    whatever storage format was previously defined for the column partition if the partition did not have a system‑determined format.

    system‑determined is the storage format that was previously defined as system‑determined.

  • If the altered column partition that contains the column has system‑determined column partition format, Teradata Database redetermines the column partition format and then alters the column partition to that format if it does not already have that format.