Oracle Scratchpad

April 25, 2014

Modify PK – 2

Filed under: Indexing,Oracle — Jonathan Lewis @ 5:48 pm BST Apr 25,2014

In an earlier posting I described how we can play games with “primary key” indexes in 12c because you can create multiple indexes on a table for the same (ordered) column list provided they have some differences in attributes and only one of them is visible at a time. But how, if you’re not on 12c, can you change a primary key index from unique to non-unique (or vice versa, as this question on OTN wants) without any down-time ?

Of course you can’t “change” the uniqueness of an index – that attribute is embedded in the way that Oracle stores rowids for the index – but you can create a new index for the constraint; and you can’t avoid the little bit of “not quite down”-time that it takes to start and finish an online rebuild. But how do you get around the limitation of Oracle error: ORA-01408: such column list already indexed

Easy – though it requires you to do some work you would prefer to avoid – just work through an intermediate step using an index with an extra column. Here’s an example where I start with a non-unique index supporting the PK and end up with a unique index.

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;


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by