August 6, 2016

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:

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:

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:

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:

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.

In my opinion 11.2.0.4 is correct and shows the normal behaviour in this case. So, if we are to stick a bug on one release it would be naturally 11.2.0.3. I don’t think that, to have direct path load obeyed, you would tolerate the presence of duplicate data in your table with an enabled primary key.

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.