- 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.
The maximum is 9,223,372,036,854,775,807.
- The maximum number of partitions for a row partitioning level is at least 2.
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.
- 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 9,223,372,036, 854,775,807.
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 statement The following rules apply to the ADD clause for a row or column partition.