15.00 - Rules for Column Partitions in a Column-Partitioned Table - 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)

Rules for Column Partitions in a Column‑Partitioned Table

  • You cannot specify a column more than once within a column partition, nor can you specify a column to be in more than 1 column partition.
  • If you attempt to do either, Teradata Database aborts the request and returns an error to the requestor.

  • The number of defined column partitions for a column partitioning level is the number of user‑specified column partitions plus 2 column partitions reserved for internal use.
  • Teradata Database uses 1 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 1 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.