16.20 - Rules for Column Partitions in a Column-Partitioned Table - 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)
  • You cannot specify a column more than once within a column partition, nor can you specify a column to be in more than one column partition.
  • The number of defined column partitions for a column partitioning level is the number of user-specified column partitions plus two column partitions reserved for internal use.

    Teradata Database uses one of the reserved partitions as a sparse bit map to indicate deleted rows and the other is reserved for future use. There is always at least one column partition number that is not assigned to a column partition.

  • The number of defined partitions for a row partitioning level is the number of user-defined row partitions specified by the PARTITION BY clause.

    If you do not specify a RANGE_N or CASE_N function in the PARTITION BY clause, Teradata Database uses a maximum of 65,535 row partitions for the table by default.

  • Teradata Database initially assigns numbers to user-specified column partitions starting at 1 and increasing upward in increments of 1 up to a maximum of cs.
    The following definitions apply to this rule.
    Variable Definition
    cs the number of user-specified column partitions.
    cm the maximum partition number for the column partitioning level.

    Teradata Database assigns an internal partition a partition number of cm-1 and the delete column internal partition is assigned a partition number of cm.

    At first, no column partitions are assigned to column partition numbers cs+1 to cm-2. Initially, there is at least 1 unused column partition number because cm-cs-2 is greater than 0.

    As you drop or alter partitions, there can be gaps in the numbering. As you add or alter column partitions, unused column partition numbers between 1 and cm-2 can be assigned to the newly added or altered column partitions as long as 1 column partition number remains unused. This is necessary because at least 1 column partition number must be available for use by ALTER TABLE to alter a column partition.

    Teradata Database uses the column partition number to compute the combined partition number for a column partition value of a table row. Apart from that, there is no significance to the column partition number assigned to a column partition.