The following rules apply only to altering the partitioning expression for multilevel partitioning.
Use a RANGE_N or CASE_N Function
Each new partitioning expression must be defined using only a RANGE_N function or a CASE_N function. It is not required that the partitioning expressions for a row-partitioned table or that the row partitioning levels for a column-partitioned table or join index be all of one form or the other, only that each individual partitioning expression must be constructed from either a RANGE_N or a CASE_N function.
Maximum Number of Partitions
The product of the number of partitions defined by each new partitioning expression cannot exceed 9,223,372,036,854,775,807 for 8-byte partitioning or 65,535 for 2-byte partitioning.
For 8-byte partitioning, the maximum number of new partitioning expressions is 62. This is because 263 = 9,223,372,036,854,775,808, which is one larger than the valid upper limit on the number of combined partitions for a table or join index.
For 2-byte partitioning, the maximum number of new partitioning expressions is 15. This is because 216 = 65,536, which is one larger than the valid upper limit on the number of combined partitions for a table or join index with 2-byte partitioning.
216 - 1 = 65,535, which is the maximum number of combined partitions for 2-byte partitioning of a table or join index.
Teradata Database returns an error to the requestor when only one partition is defined for a row partitioning level with an ADD 0 or with no ADD specification and the maximum is not increased to at least 2.
Minimum of 2 Partitions per Row Partitioning Level
As a general rule, the number of partitions for a row partitioning level must be at least 2, with the following exceptions.
For each row partitioning level that does not specify an ADD clause in level order, the maximum number of partitions for the row partitioning level is as follows.
| Table or join index partitioning | Maximum number of partitions for a row partitioning level |
|---|---|
| 2-byte partitioning | Cannot cause the partitioning to be 8-byte partitioning. |
| 8-byte partitioning | Cannot exceed 9,223,372,036,854,775,807. |
The following table defines the maximum number of row partitions for a row partitioning level that define the maximum number of partitions for the first level to the largest value that does not cause the maximum combined partition number to exceed the maximum.
| Table or join index partitioning | Maximum number of partitions for a row partitioning level |
|---|---|
| 2-byte partitioning | Cannot cause the maximum combined partition number to exceed 65,535. If there is 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, if any, from the second level to the last. |
| 8-byte partitioning | Cannot 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. |
The maximum number of new partitioning expressions is 62.
If more than two partitions are defined at one or more levels, the number of new partitioning expressions can be further limited.