The following guidelines are recommended best practices for maintaining the partitioning ranges of a partitioned table.
As with any processes, you must tune the best practices listed below 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.