15.00 - COLUMN Option for Column Partitioning - 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)

COLUMN Option for Column Partitioning

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

  • If you specify a primary index and a PARTITION BY clause either in the PRIMARY INDEX specification or by itself in the index list, you cannot specify a COLUMN partitioning level in the PARTITION BY clause. Otherwise, the system returns an error to the requestor.
  • If you specify COLUMN partitioning in a PARTITION BY clause, one of the following things must also be true.
  • You also specify NO PRIMARY INDEX.
  • You specify neither PRIMARY INDEX nor NO PRIMARY INDEX and the current table definition does not have a primary index.
  • If neither of these conditions is true, Teradata Database aborts the request and returns an error to the requestor.

  • If you specify COLUMN in the PARTITION BY clause for a table or join index, the table or join index is column‑partitioned by definition.
  • If you do not specify a primary index or if you specify NO PRIMARY INDEX, but you do specify a PARTITION BY clause either in the NO PRIMARY INDEX specification or by itself in the index list, you must specify a COLUMN partitioning level. If you do not, Teradata Database aborts the request and returns an error to the requestor.
  • If 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.
  • The following rules apply to specifying column grouping for a COLUMN specification in the PARTITION BY clause.
  • Teradata Database defines a column partition for each non‑group and group column partition you specify in the column group list.
  • If 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.
  • If 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.
  • If 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.
  • If 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.
  • If 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 (defined as 256 or fewer bytes) to have COLUMN format and a wide column partition to have ROW format.

  • If 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.
  • A column partition value is the values of the columns in the column partition for a specific table row.
  • If 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.

  • 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 uncompressed 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.

  • You can explicitly specify COLUMN or ROW format if you want a specific format for a column partition.
  • If you want Teradata Database to determine the format for a column partition, specify SYSTEM.

  • 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.
  • You cannot specify a column more than one time within a column partition.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You cannot specify a column to be in more than one column partition.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.