The following statement distributes the contents of a subpartition of partition
us_locations into one or more remaining subpartitions (determined by the hash function) of the same partition. For an interval-partitioned table, you can only coalesce hash subpartitions of materialized range or interval partitions. Basically, this operation is the inverse of the
SUBPARTITION clause discussed in "Adding a Subpartition to a *-Hash Partitioned Table".
ALTER TABLE diving MODIFY PARTITION us_locations COALESCE SUBPARTITION;