It should be possible to combine multiple adjacent partitions into one partition without significant data movement. This could be implemented by stitching the B-trees together (just like parallel, offline, non-partitioned index-build does it!).
This feature should also work for schema-compatible tables which contain distinct ranges of data.
Stitching the B-trees would require
- validation of the operation (schema compatibility and adjacent data ranges)
- modifying the page pointers on O(log N) pages
- modifying the tree root page or creating a new root page
It would be an O(log N) operation making it extremely fast even on the biggest tables.
- A very valuable feature for data loading processes (ETL). One could load concurrently into multiple tables and combine them afterwards. No partitioning necessary.
- And for partitioned tables this would provide fast partition merging.
- A similar concept could be implemented for fast partition splitting and table splitting.
- One could convert an existing table into a partitioned one without rebuilding the table. Just split the existing B-tree and add the metadata for all partitions
- One could convert a partitioned table into an unpartitioned one. Again, without rebuilding.
In short, a killer feature for performance of VLDB's.