While investigating a recent performance issue I arrived at the conclusion that an index comprising of the primary key plus one other column would be required. I have previously noted that a unique constraint may be enforced using a non-unique index.
Having another index on the table that mirrored the primary key plus another column seemed wasteful so I set about removing the existing primary key index and replacing it with the new index. Thankfully Oracle provides the ability to alter an existing primary key constraint to be enforced through a different index without the need of dropping and recreating the constraint, as I’ll illustrate using the following set-up:
SQL>CREATE TABLE tab 2 (id NUMBER (6) NOT NULL 3 ,cat NUMBER (6) NOT NULL 4 ,val NUMBER (6) NOT NULL) 5 / Table created. SQL>ALTER TABLE tab 2 ADD CONSTRAINT tab_pk 3 PRIMARY KEY (id) 4 / Table altered. SQL>CREATE INDEX tab_pk2 2 ON tab (id, cat) 3 / Index created. SQL>SELECT index_name 2 , uniqueness 3 FROM user_indexes 4 WHERE table_name = 'TAB' 5 / INDEX_NAME UNIQUENESS --------------- --------------- TAB_PK UNIQUE TAB_PK2 NONUNIQUE
In the above we have a simple table with a primary key defined on the column ID, a unique index TAB_PK on the ID column and a non-unique index on the ID and CAT colums. If we were to simply try removing the unique index that’s currently enforcing the primary key constraint then we’ll be met with:
SQL>DROP INDEX tab_pk
2 /
DROP INDEX tab_pk
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
What we need to do first is to tell Oracle to enforce the primary key constraint using the non-unique index:
SQL>ALTER TABLE tab 2 MODIFY CONSTRAINT tab_pk 3 USING INDEX tab_pk2 4 / Table altered.
This is now where things get a bit more interesting. If we look at the indexes on our table we find:
SQL>SELECT index_name 2 , uniqueness 3 FROM user_indexes 4 WHERE table_name = 'TAB' 5 / INDEX_NAME UNIQUENESS --------------- --------------- TAB_PK2 NONUNIQUE
Hey, what hapened to our unique index? It seems that Oracle has dropped it. So why did that happen? As it turns out, if the index is created automatically as part of the creation of the primary key constraint then it will be automatically removed when a new index is used to enforce the constraint. If the index were created before the constraint then this doesn’t happen, as illustrated below:
SQL>SELECT index_name 2 , uniqueness 3 FROM user_indexes 4 WHERE table_name = 'TAB' 5 / no rows selected SQL>CREATE UNIQUE INDEX tab_pk 2 ON tab (id) 3 / Index created. SQL>ALTER TABLE tab 2 ADD CONSTRAINT tab_pk 3 PRIMARY KEY (id) 4 USING INDEX tab_pk 5 / Table altered. SQL>CREATE INDEX tab_pk2 2 ON tab (id, cat) 3 / Index created. SQL>ALTER TABLE tab 2 MODIFY CONSTRAINT tab_pk 3 USING INDEX tab_pk2 4 / Table altered. SQL>SELECT index_name 2 , uniqueness 3 FROM user_indexes 4 WHERE table_name = 'TAB' 5 / INDEX_NAME UNIQUENESS --------------- --------------- TAB_PK UNIQUE TAB_PK2 NONUNIQUE
In the above we created the index for enforcing the primary key before declaring the constraint. When we switched to using a new index for the constraint the original index remained.
Some simple renaming is all that remains:
SQL>ALTER INDEX tab_pk 2 RENAME TO tab_pk_old 3 / Index altered. SQL>ALTER INDEX tab_pk2 2 RENAME TO tab_pk 3 / Index altered.
I must say that I prefer the old index remaining behind after the change to the primary key constraint. Leaving the original index in place allows me the opportunity to revert the change in the unlikely event that the new index causes query execution plan problems. The original index can be marked as invisible:
ALTER INDEX tab_pk_old INVISIBLE
and should problems arise it can quickly be reinstated without having to do rebuild it from scratch, which is important for any reasonably large table:
ALTER INDEX tab_pk_old VISIBLE
If no problems arise after the change of primary key index then the old (now invisible) index can be dropped at some later point in time:
DROP INDEX tab_pk_old
Mark Hoxey 