Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jan 3 '12 at 22:37

This question came from our site for professional system and network administrators.

1 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

share|improve this answer
    
Are you the 'Bryce' who posted the Q? If so I'll try and get the accounts linked –  Jack Douglas Jan 10 '12 at 18:09
2  
No, I just saw it as a challenge and wanted to answer it! –  Phil Jan 10 '12 at 18:11
    
which you've done brilliantly - thanks for the contribution :-) –  Jack Douglas Jan 10 '12 at 18:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.