15.00 - Performance Implications of Multilevel Row Partitioning - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Performance Implications of Multilevel Row Partitioning

  • If a SELECT request specifies values for all the primary index columns, Teradata Database can determine the AMP on which the rows reside, and only a single AMP needs to be accessed.
  • If conditions are not specified on the partitioning columns, then Teradata Database can probe each combined partition to find the rows based on their hash value.

    If conditions are also specified on the partitioning columns, row partition elimination might reduce the number of row partitions to be probed on that AMP.

  • If a SELECT request does not specify the values for all the primary index columns or there is no primary index, then Teradata Database must do an all-AMP full table scan for a nonpartitioned table.
  • However, if row partitioning is defined on the table, and if you specify conditions on the partitioning columns, row partition elimination can reduce an all-AMP full file scan to an all-AMP scan of only the partitions of the combined partitioning expression that are not eliminated.

    The degree of row partition elimination that can be achieved depends on the partitioning expressions, the conditions in the query, and the ability of the Optimizer to recognize such opportunities.

    You need not specify values for all the partitioning columns in a query for row partition elimination to occur. Row partition elimination occurs at each level independently; the combination of the row partition elimination, if any, for each level determines which combined partitions need to be processed.