Rules for Specifying Column Grouping in a Column-Partitioned Table - Advanced SQL Engine - Teradata Database

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.

If you attempt to specify a column by something other than its name as specified in the column list, the system returns an error to the requestor.

• A column partition either has COLUMN format or it has ROW format.

  You cannot a mix both formats in the same column partition.

  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 each column as 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 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.
• 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 statement. 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.