Multilevel Partitioning | CREATE TABLE | Teradata Vantage - 17.10 - Restrictions for Multilevel Partitioning - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
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.