Oracle: How to create a “reverse” index primary key

You'd use ALTER TABLE to add the primary key constraint. In Postgres you can "promote" an index using the "ALTER TABLE .. ADD table_constraint_using_index" form

Note, the index need to be unique of course for a primary key

ALTER TABLE my_table 
    ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;

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