- Vantage 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 pi and ddi are defined as in the preceding definition for a combined partitioning expression.
Partitioning Level Expanded Summation 1 p 1 2 (p1-1)*dd1+p2 3 (p1-1)*dd1+(p2-1)*dd2+p3 4 (p1-1)*dd1+(p2-1)*dd2+(p3-1)*dd3+p4 5 (p1-1)*dd1+(p2-1)*dd2+(p3-1)*dd3+(p4-1)*dd4+p5 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:
combined_part_number-specific_col_part_value_of_row = cpn + (c - 1) * dd i
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, Teradata recommends putting the column-partitioning level before any row partitioning. Considerations that may 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 called 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 may 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.