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

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
  • When you do not specify a column grouping for a COLUMN specification, Vantage defines a separate column partition for each column and column group specified in the column list for a CREATE TABLE statement.
  • 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 statement.

    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.

  • 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 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, Vantage treats each column as a single-column partition with autocompression and system-determined COLUMN or ROW format.
    • Vantage 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 Vantage 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 Vantage 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.
  • 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 statement.
  • The following table presents the rules for using the ALL BUT option to group columns.
    IF … THEN Vantage
    you specify ALL BUT
    • defines a single-column partition with autocompression, unless default is NO AUTO COMPRESS, for any column that is not specified in the column group list.
    • 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.