Repartition Primary Key Index online in Oracle

Question

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?

Answer 1

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 :)

Phil