Single Statement Table Partitioning: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 9

It’s a question that frequently comes up on Ask TOM:

“How do I convert a non-partitioned table to a partitioned one?”

Before 12.2 this was a convoluted process. You had to create a partitioned copy of the table and transfer the data over.

You could use DBMS_redefinition to do this online. But it was a headache and easy to get wrong.

In Oracle Database 12c Release 2 it’s easy. All you need is a single alter table command:

create table t ( x int, y int, z int ); 
alter table t modify 
  partition by range (x) interval (100) (   
    partition p1 values less than (100) 
  ) online;

And you’re done!

“But what about all the indexes?” I hear you cry. Well, you can convert them too!

Just add an “update indexes” clause and state whether you want them to be local or global after the conversion:

create index iy on t (y); 
create index iz on t (z); 
alter table t modify 
  partition by range (x) interval (100) (
    partition p1 values less than (100) 
  ) update indexes ( 
    iy local, iz global 
  );

If you really want, you can give your global indexes different partitioning scheme.

While you can change from a non-partitioned table to partitioned, you can’t go back again. You also can’t change the partitioning scheme, e.g. to go from list to range. Try to do so and you’ll get:

ORA-14427: table does not support modification to a partitioned state DDL

But if you want to get really fancy, you can go direct from a normal table to one with subpartitions too!

alter table t modify 
  partition by range (x) interval (100) subpartition by hash (y)   
  subpartitions 4 ( 
    partition p1 values less than (100) 
  ) online;

Full article originally published at blogs.oracle.com on November 10, 2016.