ORA-14098 – Part 2: Primary Key vs. Unique Index

A couple of days ago I wrote about ORA-14098 during an “exchange partition” operation. The workaround was to simply disable the primary key constraint on both tables. But as I learned, this has some trade-offs that I want to show you.

Fact 1: If a primary constraint on a table is created without having a matching index in place, this index is automatically created by the database. Knowing that, one can define index attributes with the “using index” clause.

Fact 2: If a matching index is already in place at the time when a primary constraint is added to a table, the database does not create another index but uses the existing one to enforce the primary key constraint.

Given the sitation described in “Fact 1”, the database drops the corresponding index when disabeling the primary key constraint:

SQL> create table with_pk(
  2    id number
  3  );

Table created.

SQL> alter table with_pk add constraint c_with_pk primary key (id);

Table altered.

SQL> insert into with_pk values (1);

1 row created.

SQL> insert into with_pk values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> col index_name format a10
SQL> select index_name, status from user_indexes where table_name='WITH_PK';

INDEX_NAME STATUS
---------- --------
C_WITH_PK  VALID

SQL> alter table with_pk modify constraint c_with_pk disable;

Table altered.

SQL> select index_name, status from user_indexes where table_name='WITH_PK';

no rows selected

SQL> alter table with_pk modify constraint c_with_pk enable;

Table altered.

SQL> select index_name, status from user_indexes where table_name='WITH_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITH_PK       VALID

Not nice, the index gets dropped and re-created during the disable/enable of the primary key constraint. There were only two rows in my example, but there can be billions of rows in real-life scenarios where index creation can consume a lot of time.

So let’s have a look at “Fact 2”, the behaviour is quite different:

SQL> create table without_pk(
  2    id number
  3  );

Table created.

SQL> create unique index c_without_pk on without_pk(id);

Index created.

SQL> alter table without_pk add constraint c_without_pk primary key (id);

Table altered.

SQL> insert into without_pk values (1);

1 row created.

SQL> insert into without_pk values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name, status from user_indexes where table_name='WITHOUT_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITHOUT_PK    VALID


SQL> col index_name format a15
SQL> select index_name, status from user_indexes where table_name='WITHOUT_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITHOUT_PK    VALID

SQL> alter table without_pk modify constraint c_without_pk disable;

Table altered.

SQL> select index_name, status from user_indexes where table_name='WITHOUT_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITHOUT_PK    VALID

SQL> alter table without_pk modify constraint c_without_pk enable;

Table altered.

In this case the index survived the disable/enable procedure of the primary key constraint.

What does that mean for my exchange partition procedure? With “Fact 1” I can exchange partitions when disabeling the primary key constraints in the first place. But that means my indexes are gone. On the other hand, with “Fact 2” I still can not exchange partitions even when the primary key constraints are disabled. The corresponding indexes need to be dropped to successfully exchange partitions. That is a lose-lose situation.

All this applies to Oracle RDBMS 12.1.0.2

SQL> select banner from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production