Combined Partitioning Expressions - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
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.



    where:

    Equation element Description
    p i Row partitioning expression at level i, numbering from left to right, or 1 for a column partitioning level.
    dd i
    Constant value equal to the following product.

    d j 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 p i and dd i are defined as they were in the preceding definition for a combined partitioning expression.

    Partitioning Level Expanded Summation
    1 p 1
    2 (p 1-1)*dd 1+p 2
    3 (p 1 -1)*dd 1+(p 2-1)*dd 2+p 3
    4 (p 1-1)*dd 1+(p 2-1)*dd 2+(p 3-1)*dd 3+p 4
    5 (p 1-1)*dd 1+(p 2-1)*dd 2+(p 3-1)*dd 3+(p 4-1)*dd 4+p 5

    The combined partitioning expression reduces to p 1 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.


    Combined partition for specific column value equation

    where:

    Equation Element Description
    combined_part_number-specific_col_part_value_of_row Combined partition number for a specific column partition value of a table row.
    cpn Combined partition for the row.
    c Column partition number for this column partition value within the row.
    dd i 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. However, a large number of row partitions for the combined partitioning expression allows for finer row partition elimination. See Determining the Partitioning Granularity.