15.00 - Guidelines for Row Partitioning - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Guidelines for Row Partitioning

The key guideline for determining the optimum granularity for the row partitions of a partitioned table is the nature of the workloads that most commonly access the PPI table or join index. The higher the number of row partitions you define for a partitioned table, the more likely an appropriate range query against the table will perform more quickly, given that the partition granularity is such that the Optimizer can eliminate all but one partition.

On the other hand, it is generally best to avoid specifying too fine a partition granularity. For example if query workloads never access data at a granularity of less than one month, there is no benefit to be gained by defining partitions with a granularity of less than one month. Furthermore, unnecessarily fine partition granularity is likely to increase the maintenance load for a partitioned table, which can lead to overall system performance degradation. So even though too fine a partition granularity itself does not introduce performance degradations, the underlying maintenance on such a table can indirectly degrade performance.

You should also consider the following items if a table you are designing is planned to support tactical queries. By knowing the specifics of your workloads, you can optimize your partitioning to best suit the queries in those workloads.

  • For all-AMP tactical queries against partitioned tables, you should specify a constraint on the partitioning column set in the WHERE clause.
  • If a query joins partitioned tables that are partitioned identically, using their common partitioning column set as a constraint enhances join performance still more if you also include an equality constraint between the partitioning columns of the joined tables.