17.10 - Rules for the Maximum Partition Numbers and Partitioning Levels of a Column-Partitioned Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
  • The maximum partition number for row partitioning is the same as the maximum number of partitions for that level.
  • The maximum partition number for a column partitioning level is 1 more than the maximum number of column partitions for that level.

    This ensures that there is at least 1 unused column partition number that is always available for altering a column partition.

  • Specifying a maximum number of partitions for a partitioning level that is larger than the number of defined partitions for that level can enable the number of defined partitions for that level to be increased using an ALTER TABLE statement.
    This does not mean that you can increase the maximum number of partitions for a level.
  • The maximum number of partitions for a row partitioning level must be at least 2.

    An error occurs when only 1 partition is defined for a row partitioning level with an ADD 0 or with no ADD option and the maximum is not increased to at least 2.

  • The number of combined partitions for a table is the product of the number of partitions defined by each partitioning level.
  • The maximum number of combined partitions for a table is the product of the maximum number of partitions defined for each partitioning level.

    For single-level partitioning, the maximum number of combined partitions is the same as the maximum number of partitions for the partitioning level.

    If a table is defined with column partitioning, the maximum number of combined partitions is smaller than the maximum combined partition number. Otherwise, it is the same.

  • The maximum combined partition number for a table is the product of the maximum partition numbers for each partitioning level, up to 9,223,372,036, 854,775,807.

    For single-level partitioning, the maximum number of combined partitions is the maximum number of partitions for this partitioning level.

  • If the maximum combined partition number for a table is greater than 65,535, Vantage the partition number in an 8-byte field in the row header. This is referred to as 8-byte partitioning.

    If the maximum combined partition number for a table is less than or equal to 65,535, Vantage stores the partitioning in a 2-byte field in the row header. This is referred to as 2-byte partitioning.

    Single-level column partitioning with no ADD option is stored in a 2-byte field in the row header.

  • For 2-byte partitioning, the maximum number of partitions for the first level is the largest value that does not cause the maximum combined partition number to exceed 65,535. This is repeated for each of the other levels, if any, from the second level to the last level.
  • For 2-byte partitioning, the maximum number of partitioning levels is 15.
  • For 8-byte partitioning, the maximum number of partitions for the first level is the largest value that does not cause the maximum combined partition number to exceed 9,223,372,036,854,775,807. This is repeated for each of the other levels from the second level to the last level.
  • For 8-byte partitioning, the maximum number of partitioning levels is 62.

    If the maximum column partition number is more than 2 at 1 or more levels, the number of partitioning levels may be further limited because of the limit placed by the rule that the product of the maximum combined partition numbers at each level cannot exceed 9,223,372,036,854,775,807.