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
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
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