15.00 - Combined Partitioning Expressions - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Combined Partitioning Expressions

This topic describes what combined partitioning expressions are and how Teradata Database uses them.

  • Teradata Database derives a combined partitioning expression from the partitioning expressions defined for each individual row partitioning level and a column partition number of 1 for the column‑partitioning level.
  • For n partitioning levels, where n is the number of partitioning levels defined, the combined partitioning expression is defined as follows.



    Equation element …

    Specifies …


    the row partitioning expression at level i, numbering from left to right, or 1 for a column partitioning level.



    a constant value equal to the following product.


    the maximum partition number defined at level j.

    The parentheses delimiting the summation are not included in the combined partitioning expression. For example, this summation expands for 1-, 2-, 3-, 4-, and 5-level partitioning as the following combined partitioning expressions, respectively, where pi and ddi are defined as they were in the preceding definition for a combined partitioning expression.


    Partitioning Level

                                                 Expanded Summation











    The combined partitioning expression reduces to p1 for single-level row partitioning, so there is no actual change in the usage rules for this case.

    For column partitioning, the combined partition number for a specific column partition value of a table row can be derived from the combined partition number for this table row as follows.



    Equation element …

    Specifies …


    the combined partition number for a specific column partition value of a table row.


    the combined partition for the row.


    the column partition number for this column partition value within the row.


    a constant value equal to 1.

    This is the same as computing the combined partitioning expression for a row‑partitioned table row except instead of using 1 for the column-partitioning level, this equation uses the column partition number corresponding to the specific column partition value of the table row.

    While column partitioning can be defined at any level, it is recommended in most cases to put the column-partitioning level first before any row partitioning. Some considerations that might lead to assigning the column partitioning to a lower level in the partitioning hierarchy are potential improvements for cylinder migration and temperature-based block compression effectiveness for hot and cold data.

    The combined partitioning expression defines how rows are ultimately partitioned on each AMP. The result of the combined partitioning expression for specific values of the partitioning column is referred to as the combined partition number.

  • With multilevel partitioning, the number of partitions defined by the combined partitioning expression is likely to be large. If there is a large number of populated partitions, performance of primary index access, joins, and aggregations on the primary index might be degraded. Therefore, multilevel partitioning may not be an appropriate choice when these operations occur frequently without significant simultaneous row partition elimination. Note, however, that a large number of row partitions for the combined partitioning expression allows for finer row partition elimination (see “Determining the Partitioning Granularity” on page 642).