Oracle database, converting unique index to non-unique one

Viewed 20837

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?

2 Answers

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.

Related