15.00 - Rules for Specifying Column Grouping in 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)

Rules for Specifying Column Grouping in a Column‑Partitioned Table

  • When you do not specify a column grouping for a COLUMN specification, Teradata Database defines a separate column partition for each column and column group specified in the column list for a CREATE TABLE request.
  • Grouping columns in a COLUMN specification of the PARTITION BY clause enables you to specify which partitions columns belong to and also enables you to specify the display order of those columns when selecting the columns from the table when you specify an ASTERISK character in the select list.
  • You can also group columns in the column list for a table. Grouping in the column list allows for a simpler, but less flexible, specification of column groupings than grouping them in the COLUMN specification of a PARTITION BY clause.

    You cannot group columns in both the column list and the PARTITION BY clause.

  • When you specify a column grouping for a COLUMN specification, you can only specify the name of columns that are defined in the same CREATE TABLE request.
  • If you attempt to specify a column by something other than its name as specified in the column list, Teradata Database aborts the request and returns an error to the requestor.

  • The following column grouping rules apply when the grouping is defined in the column list of the table definition rather than in the PARTITION BY clause.
  • A column partition either has COLUMN format or it has ROW format.
  • You cannot a mix both formats in the same partitioning level of a partitioning expression.

    However, different partitions of a column‑partitioned table can have different formats. For example, the partitions of a column‑partitioned table can have all COLUMN format and be stored in containers, all ROW format and be stored in subrows, or 1 using COLUMN format and the others using ROW format.

  • If you specify COLUMN as a partitioning level of a PARTITION BY clause, it does not specify column grouping, and if a column definition is not delimited by parentheses defining a group, Teradata Database treats the partitioning level as group defining a single-column partition with autocompression and system‑determined COLUMN or ROW format.
  • Teradata Database defines a column partition for each non‑group and group column partition specified in the column group list.
  • To specify the column partition format (COLUMN or ROW) or NO AUTO COMPRESS for a column partition defined on a single column, the column definition must be delimited by parentheses as a single-column group.

    You can also group column definitions into a single column partition using parentheses to delimit the group.

    You can optionally group one or more column definitions in a COLUMN specification in the PARTITION BY clause.

  • If you specify a column grouping with COLUMN format in the column list, the grouping defines a column partition, and Teradata Database stores one or more column partition values in a physical container row.
  • If you specify a column grouping with ROW format in the column list, the grouping defines a column partition and only one column partition value is stored in a physical subrow.
  • If you specify neither COLUMN format nor ROW format for a column list column grouping, the grouping defines a column partition and Teradata Database determines whether to assign COLUMN or ROW format to it.
  • You can only specify column grouping in the column list clause if you also specify COLUMN partitioning in the PARTITION BY clause.
  • This means that you cannot group columns or constraints in the column list for tables that are not column‑partitioned.

  • You cannot specify column grouping in both the column list and in the COLUMN option of the PARTITION BY clause of the same CREATE TABLE request. All grouping must be specified in the PARTITION BY clause of the request or it must all be specified in the column list of the request.
  • You can specify either the same or different column grouping for a target table as the source table has in the column list of a CREATE TABLE … AS request.
  • The following table presents the rules for using the ALL BUT option to group columns.
  •  

    IF …

    THEN Teradata Database …

    you specify ALL BUT

    1 Defines a single‑column partition with autocompression.

    2 Defines a system‑determined COLUMN or ROW format for any column that is not specified in the column group list.

    do not specify ALL BUT

    groups any columns that you do not specify in the column group list into 1 column partition with autocompression and a system‑determined COLUMN or ROW format.