16.20 - COLUMN Option for Column Partitioning - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

General Guidelines for Column Partitioning

The following rules apply to the COLUMN specification for column partitioning:

  • You cannot specify a column more than one time within a column partition.
  • You cannot specify a column to be in more than one column partition.
  • A column partition value consists of the values of the columns in the column partition for a specific table row.
  • When the COLUMN or ROW format is system-determined, either because you did not explicitly specify COLUMN or ROW format or because you explicitly specified SYSTEM format, Teradata Database bases its choice on the size of a column partition value for the column partition and other factors such as whether a column partition value for the column partition has fixed or variable length and whether the column partition is a single-column or multicolumn partition.

    As a general rule, a narrow column partition is determined to have COLUMN format and a wide column partition is determined to have ROW format.

    Teradata Database considers a column partition to be narrow when its size is about 256 bytes or less. Anything else is judged to be wide.

    Teradata Database estimates the size of a variable-length column in a column partition as its (maximum length / 3) + 2. This value depends on other factors that might make it smaller or larger, and is also subject to change if a more appropriate value is determined.

    This size is chosen based on the number of noncompressed column partition values that could fit into a container, and this depends on how large containers are allowed to grow.

    To be narrow, a large enough number of column partition values can be packed into a container to get row header compression benefits that offset any negative impacts from packing values into a container and retrieving values from a container.

    You can use HELP COLUMN requests or retrieve the information using an appropriate data dictionary view to determine the format that Teradata Database selects for a column partition.

  • A column partition has COLUMN format, ROW format, or SYSTEM format, but never a mix of formats. Different column partitions of a column-partitioned table or join index can have different formats. The column partitions of a column-partitioned table can have all COLUMN format, all ROW format, all SYSTEM format, or a mixture of formats.

Using COLUMN Grouping in the PARTITION BY Clause

The following rules apply to specifying column grouping for a COLUMN specification in the PARTITION BY clause.

If... ...Then Otherwise,
you specify COLUMN in the PARTITION BY clause for a table or join index, the table or join index is column-partitioned by definition.  
you specify MODIFY NO PRIMARY and you specify a PARTITION BY clause, you must specify a COLUMN partitioning level. Teradata Database returns an error to the requestor.
you specify non-group and group column partitions in the column group list, Teradata Database defines a column partition for each non-group and group column partition.  
you do not specify a column grouping for a COLUMN clause, Teradata Database defines a column partition for each column and column group specified in the column list for a CREATE TABLE request, or in the select list for a CREATE JOIN INDEX request.  
you specify ALL BUT with the column grouping specification, Teradata Database defines a single-column partition with autocompression and a system-determined format of either COLUMN or ROW, depending on the column characteristics, for each column not specified in the column group list.  
you do not specify ALL BUT with the column grouping specification, Teradata Database groups any columns not specified in the column group list into one column partition with autocompression and a system-determined format of either COLUMN or ROW, depending on the column characteristics.  
you specify COLUMN for a column partition, Teradata Database stores one or more column partition values in a physical row called a container using COLUMN format.  
you specify ROW for a column partition, Teradata Database stores only one column partition value in a physical row as a subrow. Subrow, or ROW, format is the standard way that Teradata Database stores rows.  
you specify SYSTEM for a column, Teradata Database determines 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 to have COLUMN format and a wide column partition to have ROW format.
 
you want Teradata Database to determine the format for a column partition, specify SYSTEM. you can explicitly specify COLUMN or ROW format if you want a specific format for a column partition.
you explicitly specify AUTO COMPRESS or NO AUTO COMPRESS for a column partition, Teradata Database either does or does not apply autocompression for physical rows according to the specification.

Teradata Database does apply any user-specified compression and, for column partitions with COLUMN format, row header compression.

 
you want to determine the format that Teradata Database selects for a column partition, use HELP COLUMN requests or retrieve the information using an appropriate data dictionary view.  
you specify neither COLUMN nor ROW for a column partition or if you specify SYSTEM, Teradata Database determines whether to use COLUMN or ROW format depending on the column characteristics.