I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the primary key on this particular table and in order to reduce the index contention I want to repartition the primary key index to have 8 hash partitions (currently the index and table are not partitioned). Can I rebuild the index converting it to partitioned in an online fashion, i.e. not stopping DML on the table?
migrated from serverfault.com Jan 3 '12 at 22:37
After much thought, I think I've managed it. Only drawback is that you'll need double the space of the underlying table to do so...
Don't think there's any other way to do it as you can't redefine PKs without dropping and recreating (correct me if I'm wrong).
Feel free to correct any mistakes I've made :)