The following rules apply only to altering the partitioning expression for multilevel partitioning.
Use a RANGE_N or CASE_N Function
Define each new partitioning expression using only a RANGE_N function or CASE_N function.
The partitioning expressions for a row-partitioned table or the row partitioning levels for a column-partitioned table or join index need not have the same form.
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.
The system 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
Typically, 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.