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

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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.