18c merge partition online

One of the cool things in 18c is the ability to merge partitions without causing a service interruption.  Here’s a video demonstration of that in action:

This is just an accompanying blog post to let you grab the scripts for the demo so that you can try this yourself on livesql.oracle.com, or any of the Oracle Cloud services that will be running 18c in the near future.

But also, make sure that you watch to the end of the video, because I also discuss some of the opportunities that this new feature provides.  I would contend that 99% of people using partitioning in their real world applications rarely change the size or structure of their partitions.  With online merge, you now have much more freedom in those choices.  For analytics, you might now be able to run workloads against your live Production transaction processing systems by utilizing a fine-grained partitioning regime, and later merging those partitions to become larger granules over time.

So try to think “outside of the box” on this one.  There could be a lot of cool benefits for you that you might not have initially considered.  The true feature is not “just” merge online – it is that you have new partitioning designs and implementations at your fingertips.

 create table sales
   ( ts        date,
     id        int,
     amt       number,
     completed varchar2(1)
   )
 partition by range ( ts )
 interval ( numtodsinterval(1,'DAY') )
 (
   partition p1 values less than ( date '2017-01-01' ),
   partition p2 values less than ( date '2017-02-01' ),
   partition p3 values less than ( date '2017-03-01' ),
   partition p4 values less than ( date '2017-04-01' ),
   partition p5 values less than ( date '2017-05-01' )
 );

 insert /*+ APPEND */ into sales
 select date '2017-01-01' - 10 + rownum / 10000 ,rownum, dbms_random.value(1,100), null
 from dual
 connect by level < 10000*120;


 exec dbms_stats.gather_table_stats('','SALES') 

 select partition_name, num_rows
 from user_tab_partitions
 where table_name = 'SALES'
 order by partition_position;

 create index sales_ix on sales ( id );

 alter table sales merge partitions p3, p4 
     into partition p3a update indexes ONLINE;