Determining the Partitioning Granularity - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

The key guideline for determining the optimum granularity for the partitions of a partitioned table is the nature of the workloads that most commonly access the partitioned table or join index. The higher the number of partitions you define for a table or join index, the more likely an appropriate range query against the database object will perform more quickly, given that the partition granularity is such that the Optimizer can eliminate all but 1 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 degradation of overall system performance. In the end, even though too fine a partition granularity itself does not itself introduce performance degradations, the underlying maintenance on such a table can indirectly degrade performance.