15.00 - Maximum Number of Partitions and Combined Partitions for a Table or Join Index - 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)

Maximum Number of Partitions and Combined Partitions for a Table or Join Index

The following rules apply to the maximum number of partitions and the maximum number of combined partitions for a table or join index.

  • Teradata Database uses column partition numbers to compute the combined partition number for a column partition value of a table row. Apart from that application, the column partition number assigned to a column partition has no significance. The insignificance of column partition numbers becomes particularly evident when you realize that the column partition number that Teradata Database assigns to a column partition changes when it is altered.
  • Define cs as the number of user‑specified column partitions and cm as the maximum partition number for the column-partitioning level.

    Teradata Database initially assigns user-specified column partitions a partition number starting at 1 and moving upward in increments of 1 up to cs.

    Teradata Database assigns a partition number of cm-1 to an internal partition and assigns the delete column partition a partition number of cm. No column partitions are assigned initially to column partition numbers cs+1 through cm-2. The initial value of cm-cs-2 is greater than 0. This means that there is at least one unused column partition number.

    As you drop or alter partitions, gaps in the numbering can occur. As column partitions are added or altered, unused column partition numbers between 1 and cm-2 can be assigned to the newly added or altered column partitions as long as one column partition number remains unused. This is because at least one column partition number must always be available to enable an ALTER TABLE request to alter a column partition.

  • Increasing the number of combined partitions might degrade the performance of access, joins, and aggregations on the primary index of a partitioned table, but enables finer row partition elimination.
  • Increasing the number of combined partitions can increase the size of a partitioned table.
  • Increasing the number of combined partitions might degrade performance for a column‑partitioned table or a table that combines column partitioning with row partitioning.
  • You cannot alter a table with 2‑byte partitioning to have 8‑byte partitioning.
  • You cannot alter a table with 8‑byte partitioning to have 2‑byte partitioning even if the number of combined partitions decreases to fewer than 65,536.
  • You should always consider maintaining a maximum number of partitions for a partitioning level that is larger than the number of defined partitions for that level to allow the number of defined partitions for that level to be increased using a later ALTER TABLE request.
  • You cannot increase the maximum number of partitions for a level.

  • The maximum partition number for a row partitioning level is the same as the maximum number of partitions for that level.
  • The maximum partition number for a column partitioning level is 1 greater than the maximum number of partitions for that level. This ensures that at least one unused column partition number is always available for altering a column partition.
  • The maximum combined partition number for a partitioned table or join index is the product of the maximum partition number determined for each partitioning level.
  • Its value must not exceed 9,223,372,036, 854,775,807. If it does, Teradata Database aborts the request and returns an error to the requestor.

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

  • The number of defined partitions for a column partitioning level is the number of user‑specified column partitions for that level plus 2 for column partitions reserved for internal use.
  • The number of combined defined partitions for a partitioned table or join index is the product of the number of defined partitions for each partitioning level.
  • The maximum number of combined partitions for a partitioned table or join index is the product of the maximum number of partitions determined 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 single partitioning level.

    If a table or join index is column‑partitioned, the maximum number of combined partitions is smaller than the maximum combined partition number because the maximum partition number is 1 greater than the maximum number of partitions for that level; otherwise, it is the same.

  • If the maximum combined partition number is greater than 65,535, the partitioning is an 8-byte partitioning; otherwise, it is a 2-byte partitioning.
  • Single-level column partitioning with no ADD option is always a 2-byte partitioning.

  • For a table or join index with 2-byte partitioning, the maximum number of partitions for the first level is increased to the largest value that does not cause the maximum combined partition number to exceed 65,535 if it is not already.
  • If there at least one level with an explicit ADD clause, there is at least one level that consists solely of a RANGE_N function with BIGINT data type, or there is column-partitioning, this is repeated for each of the other levels, from the second level to the last.

  • For a table or join index with 2-byte partitioning, the maximum number of partitioning levels is 15.
  • For a table or join index with 8-byte partitioning, the maximum number of partitions for the first level is increased to the largest value that does not cause the maximum combined partition number to exceed 9,223,372,036,854, 775,807 if it is not already. This is repeated for each of the other levels, from the second level to the last.
  • For a table or join index with 8‑byte partitioning, the maximum number of partitioning levels is 62.
  • If the maximum column partition number is more than 2 at one or more levels, the number of partitioning levels may be further limited because the maximum combined partition number must not exceed 9,223,372,036, 854,775,807.