15.00 - Restrictions for Multilevel Partitioning - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Restrictions for Multilevel Partitioning

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” on page 625, all of which also apply to multilevel partitioning.

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, you should consider the usefulness of defining such a partitioning scheme. The most useful partitioning expressions are generally those that have only a single reference to a column that is not referenced in the other partitioning expressions of the primary index.

  • 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.