SQL & PL/SQL

From the documentation on "SKIP_UNUSABLE_INDEXES" :

If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

Hemant K Chitale

a unique index can not be skipped during inserts. For bitmap and non-unique B*Tree indexes the strategy of setting the indexes unusable and rebuild them after the load works fine - but not for unique indexes. You could define the PK-Constraints using non-unique indexes, but that should of course be tested thoroughly.

If you now ask why a unique index can not be skipped then I have to answer: I don't know. I think it should be possible to defer the evaluation of uniqueness until after the load - but perhaps Oracle thinks that this could bring a lot of unnecessary work if there were indeed duplicates (making the index useless).

As far as I know there is a document "Error: ORA-01502 occurs only on unusable unique index with SKIP_UNUSABLE_INDEXES=TRUE? [ID 272565.1]" - but I have currently no MOS access to check this.

Message was edited by: Martin Preiss

Thanks, Hemant and Martin.

As Martin and Hermant have already pointed it out, skipping unique indexes is not possible even when the skip_unusable_indexes parameter is set to TRUE.

You can instead do this

(1) disable unique constraint (or primay key)

(2) set skip_unusable_indexes = true ( for other non unique indexes)

(3) load your data

(4) enable your unique (primary constraint) using the exceptions table as shown in the following blog article

     On constraint validation : use of Exception table | Mohamed Houri’s Oracle Notes

(5) eventuelly delete duplicate rows (if any) using the corresponding rowid in that exceptions table

By the way, when loading data using SQLLoader with the option direct = true, SqlLoader, behind the scene disable the unique index without disabling the corresponding constraint and allow duplicate rows to be inserted in the table to see how the sqlloader process manage to do that

http://hourim.wordpress.com/2011/04/09/sql-loader-direct-path-and-duplicate-key/

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

INDEX_NAME                     STATUS

------------------------------ --------

T1_PK                          UNUSABLE

SQL> select constraint_name, status from user_constraints where table_name = 'T1';

CONSTRAINT_NAME                STATUS

------------------------------ --------

T1_PK                          ENABLED

So for sure there is an option that allow inserting data only by disabling the unique index. For that i have to generate a trace file during the sqlloader load in a direct path load mode

Best regards

Mohamed Houri

Using this strategy, it might be a good idea to create non-unique indexes to enforce the unique constraints.

This means the index could always be rebuilt, and then used to check the constraint as it was re-enabled.

If I recall correctly, Oracle adds the hint /*+ skip_unq_unusable_idx */  to the SQL - but uses a hidden call to set a flag that makes this hint valid before using it.

Regards

Jonathan Lewis

Mohamed Houri wrote:


By the way, when loading data using SQLLoader with the option direct = true, SqlLoader, behind the scene disable the unique index without disabling the corresponding constraint and allow duplicate rows to be inserted in the table to see how the sqlloader process manage to do that

http://hourim.wordpress.com/2011/04/09/sql-loader-direct-path-and-duplicate-key/


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


INDEX_NAME                     STATUS
------------------------------ --------
T1_PK                          UNUSABLE

SQL> select constraint_name, status from user_constraints where table_name = 'T1';

CONSTRAINT_NAME                STATUS
------------------------------ --------
T1_PK                          ENABLED

Mohamed,

the SQL*Loader behaviour can be reproduced manually using SQL only, see here:

Oracle related stuff: Primary key / unique constraints enforced using a non-unique index - 11.1.0.6 and 11.1.0.7

This isn't prevented in any version I've tested so far (I haven't tested 12c yet, but it certainly applies to 11.2), and therefore is a potential trap that one can fall in when using non-unique indexes with primary / unique keys, since you can end up with duplicates in a table although the constraint is always enabled and the unusable, non-unique index can be rebuilt without any errors.

Randolf

Randolf,

seems to have changed in 12.1. Using your example from http://oracle-randolf.blogspot.de/2008/11/primary-key-unique-constraints-enforced.html I get:

-- 12.1.0.1

drop table append_test;



create table append_test as
select
trunc(sqrt(rownum-1)) as skewed_data,
rownum-1 as id,
lpad(rownum-1,10) id_char,
rpad('x',50, 'x') as filler
from
all_objects
where 1 = 2;




-- create non-unique index
create index append_test_pk on append_test(id);




-- add primary key constraint
alter table append_test add constraint pk_append_test primary key (id);




-- make the index unusable
alter index append_test_pk unusable;




-- now perform a direct-path insert
insert /*+ append */ into append_test
select
trunc(sqrt(rownum-1)) as skewed_data,
1 as id,
--rownum-1 as id,
lpad(rownum-1,10) id_char,
rpad('x',50, 'x') as filler
from
all_objects
where rownum <= 100;
                          *
FEHLER in Zeile 1:
ORA-26026: Unique-Index TEST.APPEND_TEST_PK anfänglich in unbrauchbarem Zustand
-- i.e. ORA-26026: unique index ... initially in unusable state

The error message is a little bit strange, since the index is of course still nonunique:

select index_name
     , uniqueness
     , status 
  from user_indexes 
 where table_name = upper('append_test');

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
APPEND_TEST_PK                 NONUNIQUE UNUSABLE

But at least there are no duplicates inserted without further notice.

Martin

Randolf,

It seems to be fixed in 11.2.0.4

I copied your test code and got your results in 11.1.0.7, but the insert append failed in 11.2.0.4 with error ORA-26026: : unique index TEST_USER.APPEND_TEST_PK initially in unusable state ) - not quite the correct error since the index isn't unique, but certainly an appropriate response.  12.1.0.1 is fixed the same way.

Regards

Jonathan Lewis

Jonathan, Martin,

thanks for testing it - it's not fixed in 11.2.0.2 / 11.2.0.3 (I've just tested it again), so it seems to be something that was introduced with 12.1 resp. the backport of the fix to 11.2.0.4.

Good to know that finally it was addressed - almost five years later than the original blog post (thinking about the release date of 11.2.0.4 / 12.1).

Randolf

Randolf,

Unless I have missed something, it is fixed in 11.2.0.3

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>  insert /*+ append */ into append_test

  2      select

  3      trunc(sqrt(rownum-1)) as skewed_data,

  4      1 as id,

  5      --rownum-1 as id,

  6      lpad(rownum-1,10) id_char,

  7      rpad('x',50, 'x') as filler

  8      from

  9      all_objects

10     where rownum <= 100;

insert /*+ append */ into append_test

*

ERROR at line 1:

ORA-26028: index xxxx.APPEND_TEST_PK initially in unusable state

PS : the error message is not showing unique index

Jonathan,

I have already read about the hint (SKIP_UNQ_UNUSABLE_IDX ) in ask tom site but as far as I was googling it with the wrong syntax I failed to get to that site

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:616795500346622064

Anyway, yes it needs some internal setting to work properly because when I hinted the insert using SKIP_UNQ_UNUSABLE_IDX it is still producing an error

SQL>    insert /*+ skip_unq_unusable_idx */ into t1 select rownum from dual connect by level <= 3;

   insert /*+ skip_unq_unusable_idx */ into t1 select rownum from dual connect by level <= 3

*

ERROR at line 1:

ORA-01502: index 'xxxx.SYS_C0092000' or partition of such index is in unusable state

Mohamed Houri

Best Regards

Ce message a été modifié par : Mohamed Houri

Hi Mohamed,

that's interesting, are you sure you followed the script?

Here's a cut & paste from a plain vanilla 11.2.0.3 installation (no patches applied):

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

5 rows selected.

SQL>

SQL> drop table append_test purge;

drop table append_test purge

           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

SQL> -- blank sample table

SQL> create table append_test as

  2  select

  3  trunc(sqrt(rownum-1)) as skewed_data,

  4  rownum-1 as id,

  5  lpad(rownum-1,10) id_char,

  6  rpad('x',50, 'x') as filler

  7  from

  8  all_objects

  9  where 1 = 2;

Table created.

SQL>

SQL> -- create non-unique index

SQL> create index append_test_pk on append_test(id);

Index created.

SQL>

SQL> -- add primary key constraint

SQL> alter table append_test add constraint pk_append_test primary key (id);

Table altered.

SQL>

SQL> -- same applies to unique constraint

SQL> -- alter table append_test add constraint uq_append_test unique (id);

SQL>

SQL> -- make the index unusable

SQL> alter index append_test_pk unusable;

Index altered.

SQL>

SQL> -- now perform a direct-path insert

SQL> insert /*+ append */ into append_test

  2  select

  3  trunc(sqrt(rownum-1)) as skewed_data,

  4  1 as id,

  5  --rownum-1 as id,

  6  lpad(rownum-1,10) id_char,

  7  rpad('x',50, 'x') as filler

  8  from

  9  all_objects

10  where rownum <= 100;

100 rows created.

SQL>

SQL> -- this generates an error

SQL> -- and therefore shows that this

SQL> -- was a direct-path insert

SQL> select * from append_test;

select * from append_test

              *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL>

SQL> -- now we have non-unique data

SQL> -- in the table

SQL> -- although the primary key

SQL> -- constraint is enabled and

SQL> -- validated

SQL> commit;

Commit complete.

SQL>

SQL> -- try the same using conventional insert

SQL> insert /*+ noappend */ into append_test

  2  select

  3  trunc(sqrt(rownum-1)) as skewed_data,

  4  1 as id,

  5  --rownum-1 as id,

  6  lpad(rownum-1,10) id_char,

  7  rpad('x',50, 'x') as filler

  8  from

  9  all_objects

10  where rownum <= 100;

insert /*+ noappend */ into append_test

*

ERROR at line 1:

ORA-01502: index 'CBO_TEST.APPEND_TEST_PK' or partition of such index is in unusable state

SQL>

SQL> -- rebuild the index

SQL> alter index append_test_pk rebuild;

Index altered.

SQL>

SQL> -- attempt to re-validate the constraint

SQL> alter table append_test modify constraint pk_append_test novalidate;

Table altered.

SQL>

SQL> -- fails due to duplicates

SQL> alter table append_test modify constraint pk_append_test validate;

alter table append_test modify constraint pk_append_test validate

*

ERROR at line 1:

ORA-02437: cannot validate (CBO_TEST.PK_APPEND_TEST) - primary key violated

SQL>

SQL> spool off

SQL>

SQL>

So as you can see - the direct path inserts succeeds, the index rebuild, too, but the re-validation of the constraint fails, as posted in the original note.

In 11.2.0.4 / 12.1 the direct-path insert fails as shown above.

It would be interesting to know why you get the error message in 11.2.0.3, that I don't get.

Randolf

Randolf

Here my spool file. Please let me know where I have missed someting

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> drop table append_test purge;

Table dropped.

SQL>  create table append_test as

  2      select

  3      trunc(sqrt(rownum-1)) as skewed_data,

  4      rownum-1 as id,

  5      lpad(rownum-1,10) id_char,

  6      rpad('x',50, 'x') as filler

  7      from

  8      all_objects

  9      where 1 = 2;

Table created.

SQL>

SQL> create index append_test_pk on append_test(id);

Index created.

SQL>

SQL> alter table append_test add constraint pk_append_test primary key (id);

Table altered.

SQL>

SQL> alter index append_test_pk unusable;

Index altered.

SQL>

SQL>  insert /*+ append */ into append_test

  2      select

  3      trunc(sqrt(rownum-1)) as skewed_data,

  4      1 as id,

  5      --rownum-1 as id,

  6      lpad(rownum-1,10) id_char,

  7      rpad('x',50, 'x') as filler

  8      from

  9      all_objects

10   where rownum <= 100;

insert /*+ append */ into append_test

*

ERROR at line 1:

ORA-26028: index xxxx.APPEND_TEST_PK initially in unusable state

Best regards

Mohamed Houri

So - is this a plain vanilla 11.2.0.3 installation, or do you have patches / PSUs or similar installed?

My guess is that there is a patch available for 11.2.0.3 that includes the fix - as I said, mine is a plain vanilla 11.2.0.3 without any patches installed.

Randolf

Mohamed Houri wrote:

Randolf

Here my spool file. Please let me know where I have missed someting

Mohamed,

I think the explanation for our different results is very simple: You've obviously run the test case with "SKIP_UNUSABLE_INDEXES" set to FALSE - hence you get the (different) error message. The point of the test case was however to run with the default value of SKIP_UNUSABLE_INDEXES set to TRUE, although it's not explicitly set in the test case and not mentioned in the note - clearly an omission on my side.

Randolf

Randolf,

You are absolutely right. Thanks very much for your accurate observation

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter skip

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

skip_unusable_indexes                boolean     TRUE

SQL>  create table append_test as

  2          select

  3          trunc(sqrt(rownum-1)) as skewed_data,

  4          rownum-1 as id,

  5          lpad(rownum-1,10) id_char,

  6          rpad('x',50, 'x') as filler

  7          from

  8          all_objects

  9          where 1 = 2;

Table created.

SQL>  create index append_test_pk on append_test(id);

Index created.

SQL>  alter table append_test add constraint pk_append_test primary key (id);

Table altered.

SQL>  alter index append_test_pk unusable;

Index altered.

SQL> insert /*+ append */ into append_test

  2          select

  3          trunc(sqrt(rownum-1)) as skewed_data,

  4          1 as id,

  5          --rownum-1 as id,

  6         lpad(rownum-1,10) id_char,

  7          rpad('x',50, 'x') as filler

  8          from

  9          all_objects

10     where rownum <= 100;

100 rows created.

Best regards

Mohamed Houri