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
