Working with Partitioning | ALTER TABLE | Teradata Vantage - 17.10 - COLUMN Option for Column Partitioning - 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)

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, the 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.

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

    The 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 the 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. The database returns an error to the requestor.
you specify non-group and group column partitions in the column group list, the database defines a column partition for each non-group and group column partition.  
you do not specify a column grouping for a COLUMN clause, the 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, the 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, the 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, the 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, the database stores only one column partition value in a physical row as a subrow. Subrow, or ROW, format is the standard way rows are stored.  
you specify SYSTEM for a column, the 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.
The database generally determines a narrow column partition to have COLUMN format and a wide column partition to have ROW format.
 
you want the 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, the database either does or does not apply autocompression for physical rows according to the specification.

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

 
you want to determine the format that the 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, the database determines whether to use COLUMN or ROW format depending on the column characteristics.