Primary Key non unique unusable index and direct path load:again

This combination of aprimary key policed by a non-unique unusable index on table direct path loaded raising error ORA-26026 has occurred again following a recent upgrade from 11.2.0.3 to 11.0.2.4

I know that many authors like Randolph Geist and Richard Foote has already wrote something about this subject. I, however, for the sake of my proper documentation, decided to summarise this issue in my blog.

The story comes when a developer sends me an e-mail saying that he has observed the following error in one of his overnight batch jobs in one of the TEST databases:

ORA-26026 : unique index xxx.t1_non_unique_idx initially in unusable state

Notice how the error message is pointing to a unique index error on a non-unique index. That was, for me, the first clue to what is really happening in the developer situation. The name of the developer index in the original error text was not so suggestive about the uniqueness of the index as the one I used in the above error message. The developer batch job was accomplishing the following steps:

• disable all non-unique indexes on the target table
• parallel direct path load into the target table

The developer says that his job was running quite smoothly in 11.2.0.3 and started failing because of the ORA-26026 following a fresh upgrade to 11.2.0.4. Here’s below the developer set-up you can use and play with at will:

create table t1 as select
         rownum              n1
        ,trunc((rownum-1/3)) n2
        ,mod(rownum,10)      n3
    from dual
    connect by level <= 1e3;

create index t1_non_unique_idx on t1(n1,n2);

alter table t1 add constraint t1_pk primary key (n1) using index;

create unique index t1_pk on t1(n1);

 select index_name, uniqueness
     from user_indexes
     where table_name = 'T1';
INDEX_NAME                     UNIQUENES
------------------------------ ---------
T1_PK                          UNIQUE
T1_NON_UNIQUE_IDX              NONUNIQUE

select
        constraint_name
       ,index_name
    from user_constraints
    where table_name = 'T1'
    and constraint_type = 'P';

CONSTRAINT INDEX_NAME
---------- ---------------------
T1_PK      T1_NON_UNIQUE_IDX

With this set-up in place the primary key constraint is policed by the non-unique index t1_non_unique_idx. The batch continues then by disabling all non-unique indexes and direct path load into the target table. Something resembling to the following piece of SQL code:

create table t2 as select * from t1;
truncate table t1;
alter index t1_non_unique_idx unusable;

Execute the following insert/select piece of code in 11.2.0.3 and you will not encounter any error:

insert /*+ append */ into t1 select * from t2;

Upgrade to 11.2.0.4 (or 12c) and you will immediately hit the ORA-26026:

ERROR at line 1:
ORA-26026: unique index C##MHOURI.T1_NON_UNIQUE_IDX initially in unusable state

In the developer case, despite the existence of a perfect unique index to cover the Primary key, this constraint was enforced via a non-unique index. It seems that the developer has created the T1_PK index after he has created the non-unique index and the primary key constraint.

To solve this issue all what I did is to change the index enforcing the primary key as follows:

alter table t1 modify constraint t1_pk using index t1_pk;

select
         constraint_name
        ,index_name
    from user_constraints
    where table_name = 'T1'
    and constraint_type = 'P';

CONSTRAINT INDEX_NAME
---------- ----------
T1_PK      T1_PK

insert /*+ append */ into t1 select * from t2;

1000 rows created.

Bottom line : when you are direct path loading data into a table after you have disabled its non-unique indexes be sure that your primary key is not enforced via one of those disabled non-unique indexes. Otherwise, depending on the Oracle release you are using, your insert will be rejected because of the ORA-26026 error.