- You can specify an ADD option for a partitioning level. The BIGINT number following the ADD keyword plus the number of partitions defined for the level is the maximum number of defined partitions allowed for that level.
The maximum is 9,223,372,036,854,775,807.
For single-level partitioning, the maximum combined partition number is the same as the maximum partition number for the partitioning level.
- If you do not specify an ADD option for a column partitioning level, and the level is the only partitioning level, the maximum number of partitions, including the 2 for internal use, is 65,534.
- If you do not specify an ADD option for a column partitioning level and the following list of items is also true, the partitioning is stored using 2 bytes in the row header and the maximum number of partitions for the column partitioning level is the number of column partitions defined plus 10. The default for this case is ADD 10.
- The table also has 1 or more row partitioning levels
- At least 1 of the row partitioning levels does not specify the ADD option
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; otherwise, the maximum number of partitions for the column partitioning level is the largest value that does not exceed 9,223,372,036, 854,775,807.
For single-level partitioning, the maximum combined partition number is the same as the maximum partition number for this partitioning level.
- For each row partitioning level that does not specify the ADD option in level order, Vantage determines the maximum number of partitions for that row partitioning level as follows.
- Using the number of row partitions defined as the maximum for this and any lower row partitioning level without an ADD clause, the partitioning is 2-byte partitioning and is the largest value that does not cause the partitioning to be 8-byte partitioning.
- Otherwise, the maximum number of partitions for this level is the largest value that does not exceed 9,223,372,036,854,775,807.
If there is no such largest value, the system returns an error to the requestor.
- You can specify ADD 0 for a partitioning level to specify explicitly that the maximum number of partitions for this level is the same as the number of defined partitions.
This is useful for a column partitioning level if you want to override the default of ADD 10 to enable other levels to have more partitions.
This can also be useful for a row partitioning level if you want a lower level that does not specify the ADD clause to have any excess partitions.
- The following table summarizes to which partitioning level any excess partitions are added after the partitions are initially assigned explicitly in the CREATE TABLE statement.
IF partitioning is … AND … THEN Vantage adds as many leftover combined partitions as possible to … single-level the only row or column partitioning level. If you specify an ADD clause, Vantage overrides it.
multilevel all the row partitioning levels have an ADD clause, but there is a column partitioning level without an ADD clause, the column partitioning level. This does not need to be added at the first partitioning level.
a column partitioning level and at least 1 of the row partitioning levels does not have an ADD clause, including the case where none of the row partitioning levels has an ADD clause specified, the first row partitioning level without an ADD clause after using a default of ADD 10 for the column partitioning level. This is repeated for each of the other row partitioning levels without an ADD clause, if any, in level order.
a column partitioning level has an ADD clause and at least 1 of the row partitioning levels does not have an ADD clause there is no column partitioning level and at least 1 of the row partitioning levels does not have an ADD clause.
This includes the case where none of the row partitioning levels has an ADD clause specified.
the first row partitioning level without an ADD clause. This is repeated for each of the other row partitioning levels without an ADD clause, if any, in level order.
all of the partitioning levels have an ADD clause or after applying any leftover combined partitions as listed in the next column of this table. the first row or column partitioning level and overrides the ADD clause for the first partitioning level if one is specified. Vantage repeats this for each of the other levels, if any, from the second level to the last level.