Over time, a partition’s data distribution may become skewed, or the manner in which
the data was originally partitioned may not suit current business requirements. Use
alter table to merge, split, or move partitions to redistribute the
data and revive the performance benefits of using partitions.
For example:
- Splitting partitions – a company divides data into four partitions according to
regions —North, South, East and West— so customer representatives have 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.
- Merging partitions – a company’s sales data is partitioned into the four yearly
quarters—Q1, Q2, Q3, and Q4. At the end of the year, the company merges the data for
the year and archives it. Merging partitions that represent a closed financial year
is efficient because sales’ data for a past year is accessed infrequently, and the
older data is most likely to be read but not updated.