Rules for Column Partitions in a Column-Partitioned Table - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™
  • 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.

    Vantage 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, Vantage uses a maximum of 65,535 row partitions for the table by default.

  • Vantage 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 Number of user-specified column partitions.
    cm Maximum partition number for the column partitioning level.

    Vantage 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.

    Vantage 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.