Over time, a partition’s data distribution might become skewed, or the manner in which the data was originally partitioned may no longer suit the current business requirements. Use alter table to merge, split, or move partitions to redistribute the data and revive performance benefits using partitions.
For example, a company may split partitions to better access its data according to four regions —North, South, East and West. The split partitions allow customer representatives fast and efficient access to their regions’ customers, independent of other regions. If sales increase in the Southern region and the customer base has expanded significantly, frequent queries involving partition scans and maintenance operations may cause the South partition to be slow and inefficient, losing out on the benefits of partitioning the customer data. In this situation, splitting the data in the South partition into two partitions, South-East and South-West, may revive performance without affecting the data in other partitions.
A company may merge partitions for better performance because their sales data is partitioned into the four yearly quarters—partitions Q1, Q2, Q3, and Q4. At the end of the year, the company merges the data for the year and archives it. Merging the partitions is efficient because the sales data for a past year is accessed infrequently, and the older data is most likely to be read but not updated.