The following set of rules applies to multilevel partitioning in addition to the universal rules set listed in Rules and Usage Notes for Partitioned Tables, all of which also apply to multilevel partitioning.
- If you specify more than 1 partitioning expression in the PARTITION BY clause, each such partitioning expression must consist of either a single RANGE_N or a single CASE_N function; otherwise, the system returns an error to the requestor.
- If you specify more than 1 partitioning expression in the PARTITION BY clause, the product of the number of partitions defined by each partitioning expression cannot be less than 4 nor can it exceed 9,223,372,036,854,775,807, inclusive. Otherwise, the system returns an error to the requestor. The minimum is four because a multilevel partitioning must, by definition, have at least 2 levels and each of those levels must have at least 2 partitions. The product is 2 x 2 = 4.
Additionally, each partitioning expression you specify must define at least 2 partitions. Otherwise, the system returns an error to the requestor.
This rule implies that the maximum number of partitioning expressions is 9,223,372,036,854,775,807. This is because 263 = 9,223,372,036,854,775,808, which is larger than the maximum number of partitions that can be defined for a single table.
If you define more than 2 partitions at 1 or more levels, the number of partitioning expressions can be limited still further.
- A partitioning expression cannot contain the system-derived columns PARTITION#L1 through PARTITION#15, inclusive.
If this condition is not satisfied, the system returns an error to the requestor.