In Oracle® Database Data Warehousing Guide 11g Release 2 (11.2), chapter "16 Maintaining the Data Warehouse" there is one confusing SQL example showing possibility of merging three partitions at a time. But as far as I know this is currently not possible in all Oracle versions supporting partitions merge, including 11.2.0.3 (such a statement will fail with ORA-1426). The example SQL statement from documentation is:
ALTER TABLE sales MERGE PARTITIONS sales_01_1998, sales_02_1998, sales_03_1998
INTO PARTITION sales_q1_1998 TABLESPACE archive_q1_1998
COMPRESS UPDATE GLOBAL INDEXES PARALLEL 4;
1) Is it true that the example is incorrect (and merging three and more partitions is not possible), or am I missing something?
2) Are there any plans to add such a functionality in future versions of Oracle (there is a Bug 5032097 : MERGE MULTIPLE PARTITIONS AT ONCE, BETTER CONTROL OVER INDEXS DURING MERGE, where it was requested in 2 ^nd^ item)?
3) Is it true that if merging two partitions using parallelism and if tablespace of resulting partitions is locally managed ASSM with autoallocate extent size, every parallel slave will start with extent size equal to INITIAL value of resulting partition segment (if this INITIAL provided in alter table .. merge partitions ... statement)? Some my experiments show result like this, but is this stable behaviour / reliable result? Would like to know what kind of extents I'll get after merge into autoallocate tablespace.
4) Is some kind of fast-merge partitions in one statement possible (or planned in future Oracle versions) which would only update dictionary / metadata marking existing extents which previously belonged to several old partitions segments as belonging to new one resulting partition segment? Current implementation requires additional space available for the time of operation and is actually doing data movement, thus requiring both I/O and CPU resources.