Turn Non Unique Index into a Unique Index

Service windows exist for a reason.

Also you can create indices online in an operational database.

Apart from using DBMS_REDEFINITION there is no other method.

Thinking of it: what is the business case to change the index?

-----------

Sybrand Bakker

Senior Oracle DBA

Obviously there are benefits to having a unique index rather than a non-unique index but what is the driver for the change?

If it were 12c you could do this easily:

Modify Primary Key Using Index | Richard Foote's Oracle Blog

What's wrong with keeping the non-unique index for the primary key constraint?

There is a business case against..... drop the primary key and the non-unique index remains so you don't need to build it again.

sybrand_b wrote:

 

Service windows exist for a reason.

Also you can create indices online in an operational database.

Apart from using DBMS_REDEFINITION there is no other method.

Thinking of it: what is the business case to change the index?

 

-----------

Sybrand Bakker

Senior Oracle DBA

Service windows may not be available very often, and you can't create an index with the same column list as an existing index until 12c.

Using non-unique indexes to support unique constraints changes the cost of queries and the volume of redo generated, so it's possible that the business case is that an earlier decision was wrong and needs to be corrected.

Regards

Jonathan Lewis

sql_coder wrote:

 

Version: 11.2.0.4.0 Enterprise Edition

 

We got a non unique Index for a primary key constraint. I would like to change that into a unique index. I know you cannot alter the index to change it. Is the only way to disable the primary key, drop the index and enable the constraint again or is there a better solution? It is a 24/7 platform.

 

If you want the index to have exactly the same column definition then you have to fiddle around a little bit (and use extra resources) if you want to avoid any down-time. The trick is to create an intermediate index to support the constraint, drop the old PK index, create a new PK index, then drop the intermediate one; e.g.

create table t1 as select * from all_objects where rownum <= 10000;

create index t1_pk on t1(object_id);

alter table t1 add constraint t1_pk primary key(object_id);

create index t1_i1 on t1(object_id, 0) online;

alter table t1 modify primary key using index t1_i1;

drop index t1_pk;

create unique index t1_pk on t1(object_id) online;

alter table t1 modify primary key using index t1_pk;

drop index t1_i1;

Regards

Jonathan Lewis