15.00 - ADD Option - 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)

ADD Option

The ADD option reserves additional partition numbers for a partitioning level to enable adding partitions to a partitioning level at a later time using an ALTER TABLE request The following rules apply to the ADD clause for a row or column partition.

  • If you specify an ADD clause for a partitioning level, the maximum number of partitions for that partitioning level is the number of defined partitions for the level plus the value of the constant specified in the ADD clause.
  • If this maximum exceeds 9,223,372,036, 854,775,807, Teradata Database aborts the request and returns an error to the requestor.

  • The maximum number of partitions for a row partitioning level is at least 2.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

    This error occurs when you define only 1 partition for a row partitioning level that also specifies ADD 0 or that does not specify an ADD option and the maximum is not increased to at least 2 in one of the following ways.

  • For single‑level partitioning, the maximum combined partition number is the same as the maximum partition number for this partitioning level. That value cannot exceed 9,223,372,036, 854,775,807.
  • For 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.
  • For 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.
  • For each row partitioning level in level order without an ADD clause where the level has 2‑byte partitioning, the maximum number of partitions for the level is the largest value that does not cause the partitioning to be 8-byte partitioning.
  • Otherwise, the maximum number of partitions for the level is the largest value that does not cause the maximum number of combined partitions to exceed 9,223,372,036,854,775,807.

    If there is no such value, Teradata Database aborts the request and returns an error to the requestor.

  • You can specify ADD 0 for a partitioning level to specify that the maximum number of partitions for the level is the same as the number of defined partitions.
  • This is useful for a row partitioning level if you want a lower level that does not specify the ADD clause to receive any excess partitions.

    This is useful for a column partitioning level if you want to override the default of ADD 10 so that other levels can have more partitions.

  • If you do not specify an ADD clause for a column partitioning level and that level is the only partitioning level, the maximum number of partitions including the 2 for internal use is 65,534
  • If the following things are all true, the maximum number of partitions for the column partitioning level is the number of column partitions defined plus 10.
  • You do not specify an ADD clause for a column partitioning level
  • The table or join index also has row partitioning
  • At least one of the row partitioning levels does not specify an ADD clause
  • In this case, the default ADD clause for the column partitioning level is ADD 10.

  • If the following things are all true, the maximum number of partitions for the column partitioning level is the largest value that does not cause the partitioning to be 8-byte partitioning.
  • You do not specify an ADD clause for a column partitioning level
  • The table or join index also has row partitioning
  • All of the row partitioning levels specify an ADD clause
  • Using the number of column partitions defined plus 10 as the maximum number of column partitions, the partitioning would be 2-byte partitioning
  • Otherwise, the maximum number of partitions for the column partitioning level is the largest value that does not cause the limit of 9,223,372,036, 854,775,807 to be exceeded.

    If there is no such largest value, Teradata Database aborts the request and returns an error to the requestor.