Best Practices for Adding and Dropping Partitioning Ranges from a Partitioning Expression - 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 following guidelines are recommended best practices for maintaining the partitioning ranges of a partitioned table.

As with any processes, you must tune the following best practices to suit your individual processes, query workloads, and performance criteria.
  • Have a well-defined, preferably automated, process for scheduling and issuing ALTER TABLE requests to add and drop partitioning ranges.
  • Collect and maintain a current, monthly PARTITION statistics report which tracks the frequency of ALTER TABLE requests for adding and dropping partitions.
  • Run and compare periodic EXPLAIN reports. This report is useful for determining the effects of your current partitioning on the performance of individual queries from your standard workloads.
  • Based on your collected PARTITION statistics and scheduling process, define enough future ranges to minimize the frequency of ALTER TABLE requests for adding and dropping partitions. You should keep the number of “future” ranges at any one time to less than 10 percent of the total number of defined partitions.
    A future range is a range over date set of values which has not yet occurred at the time the partition is added. It is used to minimize the frequency of ALTER TABLE requests for adding and dropping partitions.
  • To ensure the ALTER TABLE request does not fail as a result of infrequency, at least monthly make the necessary range drops and additions.
  • Ensure that you drop any old partitions no longer needed, especially if queries in the workloads accessing the row-partitioned table do frequent primary index accesses and joins, and the complete partitioning column set is not included in the primary index definition.