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?
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).
PHIL@PHILL11G2 > alter table bigtable add constraint bigtable_pk primary key (object_id); Table altered. PHIL@PHILL11G2 > commit; Commit complete. PHIL@PHILL11G2 > create table bigtable_interim as ( select * from bigtable where 1=0 ); Table created. PHIL@PHILL11G2 > EXEC dbms_redefinition.can_redef_table('PHIL', 'BIGTABLE'); PL/SQL procedure successfully completed. PHIL@PHILL11G2 > exec DBMS_REDEFINITION.start_redef_table(uname => 'PHIL',orig_table => 'BIGTABLE',int_table => 'BIGTABLE_INTERIM'); PL/SQL procedure successfully completed. PHIL@PHILL11G2 > PHIL@PHILL11G2 > create unique index bigtable_int_hash on bigtable_interim (object_id) global partition by hash (object_id) (partition p1 tablespace users, partition p2 tablespace users) online; Index created. PHIL@PHILL11G2 > exec dbms_redefinition.finish_redef_table(uname => 'PHIL',orig_table => 'BIGTABLE',int_table => 'BIGTABLE_INTERIM'); PL/SQL procedure successfully completed. PHIL@PHILL11G2 > select count(*) from bigtable_interim; COUNT(*) ---------- 2300640 PHIL@PHILL11G2 > select count(*) from bigtable; COUNT(*) ---------- 2300640 PHIL@PHILL11G2 > PHIL@PHILL11G2 > alter table bigtable add constraint bigtable_hashed_pk primary key (object_id) ; Table altered. PHIL@PHILL11G2 > -- that reused the hashed index i created
Feel free to correct any mistakes I've made :)
I would suggest to run copy_table_dependents & sync_interim_table before doing finish_redef_table. You might get failure on copy_table_dependents since you are creating the index outside but you still need to do it, in order to bring all the associated constraints, grants, etc. sync will help if there is some load happened after you start the redef process before you finish...especially if it is large table.