Oracle database, converting unique index to non-unique one

Question

I understand I can't do this straightforward from studying similar questions on stackoverflow and other sites.

However, I need to do this and I'm willing to go with workarounds.

I tried to create a non-unique index with online and parallel, and then drop the old unique index. However, it fails saying ORA-01408: such column list already indexed.

How to convert an unique index to a non-unique one?

Answer 1

If you don't want to drop the old index before creating the new one, you can cheat a bit by creating the new index with an additional useless column, e.g.:

Assuming a table with the following configuration:

create table mytable (id number);

create unique index myunique on mytable (id);

To convert the index to non unique:

create index temp on mytable (id, 1);

drop index myunique;

create index mynonunique on mytable (id);

drop index temp;

In practice I'm not sure how necessary this is - generally I'd just drop and recreate the index in some low-activity period, preferably take the application down.

Answer 2

Oracle now supports multiple indexes applied to the same set of columns as long as they differ in uniqueness (and/or in some other properties such as bitmap vs. btree and partitioning) and as long as only one of them is visible.

Therefore you can alter the existing index - changing it to invisible without dropping it and then create a new non-unique index.

CREATE TABLE mytable (id NUMBER);

CREATE UNIQUE INDEX mytable_unique_idx ON mytable(id);

ALTER INDEX mytable_unique_idx INVISIBLE;

CREATE INDEX mytable_nonunique_idx ON mytable(id);

Note that invisible indexes are still maintained by the database and you can change between them by turning one of them to invisible and the second one to visible.