15.00 - Best Practices for Adding and Dropping Partitioning Ranges from a Partitioning Expression - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Best Practices for Adding and Dropping Partitioning Ranges from a Partitioning Expression

The following guidelines are recommended best practices for maintaining the partitioning ranges of a partitioned table.

  • Have a well‑defined, preferably automated, process for scheduling and issuing ALTER TABLE requests to add and drop partitioning ranges.
  • Define enough future ranges (a future range is a range over date values that have not yet occurred at the time the partition is added) to minimize the frequency of ALTER TABLE requests for adding and dropping partitions.
  • If you perform this task too infrequently, you might forget to make the necessary range drops and additions, and the process might fail because it does not occur frequently enough.

    Consider the following guidelines.

  • It is generally better to perform this task monthly rather than yearly.
  • You should keep the number of “future” ranges at any one time to less than 10 percent of the total number of defined partitions.
  • This is not critical as long as you keep freshly collected PARTITION statistics.

    As always, you must tune the process to suit your individual processes, query workloads, and performance criteria. Running and comparing periodic EXPLAIN reports is useful for determining the effects of your current partitioning on the performance of individual queries from your standard workloads.

  • Ensure that you drop old partitions that are no longer needed, especially if queries in the workloads that access 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.