The following guidelines are recommended best practices for maintaining the partitioning ranges of a partitioned table.
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. Keep the number of future ranges to less than 10 percent of the total number of defined partitions.A future range is a range of dates that are in the future when the partition is added. A future range minimizes the frequency of ALTER TABLE requests for adding and dropping partitions.
- To make sure the ALTER TABLE request does not fail as a result of infrequency, at least monthly make the necessary range drops and additions.
- Drop no longer needed partitions, 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.