Changing primary key index

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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s