Mohamed Houri’s Oracle Notes

August 6, 2016

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

Filed under: direct path,Oracle,SQL Server — hourim @ 6:10 am

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.

March 14, 2014

parallel insert

Filed under: direct path — hourim @ 2:28 pm

Do you know that a direct path insert can be silently ignored when the inserted table has an insert trigger?

SQL> create table t1 as select rownum n1 from dual connect by level <=1e5;

SQL> create table t2 as select * from t1 where 0 = 1;

SQL> create or replace trigger t2_trg
      before insert on t2
      for each row
    begin
       null;
    end;
   /

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

100000 rows created.

SQL> select count(1) from t2;

COUNT(1)
---------
100000

SQL> alter trigger t2_trg disable;

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

100000 rows created.

SQL> select count(1) from t2;

select count(1) from t2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

It is only when the trigger has been disabled that the insert has been done via a direct path load.

But are you aware that a parallel insert can also be silently ignored in presence of an insert trigger?

SQL> alter session enable parallel dml;

SQL> insert /*+ parallel(t2) */ into t2 select * from t1;

100000 rows created.

SQL_ID  5npb49pus3wtr, child number 0
-------------------------------------
insert /*+ parallel(t2) */ into t2 select * from t1

Plan hash value: 2315600204
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |          |       |       |    47 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT       |          |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |   100K|  1269K|    47   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN| :TQ10000 |   100K|  1269K|    47   (7)| 00:00:01 |        | S->P | RND-ROBIN  |
|   6 |       TABLE ACCESS FULL | T1       |   100K|  1269K|    47   (7)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> select count(1) from t2;

select count(1) from t2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> rollback;

SQL> alter trigger t2_trg enable;

SQL> insert /*+ parallel(t2) */ into t2 select * from t1;

100000 rows created.

SQL_ID  5npb49pus3wtr, child number 0
-------------------------------------
insert /*+ parallel(t2) */ into t2 select * from t1

Plan hash value: 3617692013
---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |       |       |    47 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T1   |   100K|  1269K|    47   (7)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> select count(1) from t2;

COUNT(1)
----------
300000

When the trigger has been disabled the insert went through a parallel load path in contrast to when the same trigger has been enabled where the insert went through to a classical conventional load.

A classical conventional load have I said?

Yes. If you look closely to the case where the parallel insert has been honored you will realize that not only the insert run in parallel but it has also been done via a direct path mode.

Tell me: does this means that a parallel insert (when honored) is always accomplished via a direct path load?

Yes it is.

So is the append hint in the following instruction redundant?


SQL> insert /*+ append parallel(t2) */ into t2 select * from t1;

No it is not always redundant.

Simply because a parallel run and a direct path load share several common “impeachment” reasons like the presence of insert triggers or foreign integrity constraints((yes I have also tested this case also)  but they also have their proper source of impeachment. While the direct path is not a CBO decision, the parallel run is. So, what do you think will happen when the CBO decides that the parallel insert is not the best path to follow and you have already get rid of the append hint from your insert statement? The direct path, when possible, will not be followed.

If I simulate a parallel run impeachment by disabling the parallel dml and get rid of the append hint from my insert statement then the direct path load will not happen

SQL> alter session disable parallel dml;

SQL> insert /*+ parallel(t2) */ into t2 select * from t1;

100000 rows created.

SQL> select count(1) from t2;

COUNT(1)
----------
100000

However if, in the same situation,  I will add the append hint to my insert statement then the insert will follow a direct path load path as demonstrated below:

SQL> insert /*+ parallel(t2) append */ into t2 select * from t1;

100000 rows created.

SQL> select count(1) from t2;
select count(1) from t2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

This is the otn thread that prompted this blog post

Bottom line: I learnt yesterday that a parallel insert when followed by the CBO will use a direct path load which is not always the best solution. I learnt also in this case that a parallel insert suffers from the same impeachment raisons as the direct path load does: presence of insert triggers and foreign integrity constraint. And I learnt that to secure your direct path load add the append hint  to your insert statement even when this statement contains a parallel hint

April 9, 2011

SQL-Loader direct path and duplicate key

Filed under: direct path — hourim @ 5:10 pm

Very often I see on the OTN site a thread where a poster is wondering why, despite the presence of a primary key on his table, this one contains duplicate key when it is loaded via sqlloader using direct path load.

The answer to this question is that when you direct path load, the unique index supporting the primary key is disabled behind the scene allowing duplicate key to be inserted. Here below is the demonstration of this behaviour :

mhouri.world> create table t1 (id number);

Table created.

mhouri.world> alter table t1 add constraint t1_pk primary key (id);

Table altered.

mhouri.world> select table_name, index_name, status
 2  from user_indexes
 3  where table_name = 'T1';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
T1                             T1_PK                          VALID

mhouri.world> select count(1) from t1;

 COUNT(1)
----------
 0

The control file and the  sqlloader call are shown here below:

LOAD DATA
INFILE *
REPLACE
INTO TABLE T1
(
 id position(1:1) TERMINATED BY ","
)
BEGINDATA
1;
2;
2;
3;
c:\sqlldr mhouri/mhouri@mhouri control=c.ctl direct=true

mhouri.world> select count(1) from t1;</pre>
COUNT(1)
 ----------
 4

mhouri.world> select * from t1;

ID
 ----------
 1
 2
 2
 3

mhouri.world> select table_name, index_name, status
 2  from user_indexes
 3  where table_name = 'T1';

TABLE_NAME                     INDEX_NAME                     STATUS
 ------------------------------ ------------------------------ --------
 T1                             T1_PK                          UNUSABLE

mhouri.world> alter index t1_pk rebuild;
 alter index t1_pk rebuild
 *
 ERROR at line 1:
 ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

This is one of the thing that we all have to be aware of when using direct path load : uniqueness is ignored.

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“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.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)