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.