- The universal rules in Rules and Usage Notes for Partitioned Tables.
- 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 one partitioning expression in the PARTITION BY clause, the product of the number of partitions defined by each partitioning expression cannot be less than 4 or more than 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.
While a partitioning expression can have multiple column references, and a column can be referenced in more than 1 partitioning expression for the primary index, consider the usefulness of defining such a partitioning scheme. The most useful partitioning expressions have a single reference to a column that is not referenced in the other partitioning expressions of the primary index.