Adding partitions to table

Recently, we had performance issues in our SQL code and one of the ways we thought was to increase the number of partitions.

The way to do it is to give one alter table query for each partition. And also the best practice is to have the number of partitions as a power of 2. So we increased it from 8 to 16

ALTER TABLE table_name ADD PARTITION

This will add the partition and also recalculate the rows that need to be moved to the new partition.

Note: If there are Local indexes in the partitions, they are invalidated due to the new partitions added and they need to be made usable

ALTER TABLE list_customers MODIFY PARTITION asia
REBUILD UNUSABLE LOCAL INDEXES;