16.20 - Restrictions for Multilevel Partitioning - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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.

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.