The curious case of the missing index

Today we were moving some schemas from database A to database B - all was pretty much fine apart from some messages about bad index stats right at the end where it was failing to import some metadata against an object that didn't exist

ORA-39083: Objekttyp INDEX_STATISTICS konnte nicht erstellt werden, Fehler:
ORA-01403: Keine Daten gefunden
ORA-01403: Keine Daten gefunden

It's in German but i'm sure you an work it out

This is very odd - it;s a complete schema copy - how can it fail to load some index stats - it didn't complain about any indexes failing - whats going on here.....?

Well this proved to be quite tricky to track down - but eventually i got to the bottom of it and i can demonstrate with a simple test case here. I guess this is the way it's always been though it does feel slightly like a bug rather than a feature - see what you think....

So here is a case where we create a very simple table with a pk and 1 additional index

SQL>  create table system.test (col1 number,col2 number, col3 number);

Table created.

SQL> alter table system.test ADD PRIMARY KEY (col1) using index

  2  /

Table altered.

SQL>  create index system.idx1 on system.test (col1,col2);

Index created.

If we check now we can see that as expected - we have a PK constraint that is validated using the SYS_C00292333 (the index implictly created by the PK command and named the same as the constraint)

SQL>  select * from dba_constraints where table_name='TEST'  and owner='SYSTEM' and constraint_type='P'

  2  /

SYSTEM                         SYS_C00292333                  P TEST                                                                          ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED      GENERATED NAME          18.01.16 SYSTEM                         SYS_C00292333

SQL>

And we can also see we have 2 indexes

SQL> select count(*) from dba_indexes where table_name='TEST' and owner='SYSTEM'

  2  /

  COUNT(*)

----------

         2

Now this is where it gets interesting lets drop the table and change the order......

SQL> drop table system.test;

Table dropped.

SQL> create table system.test (col1 number,col2 number, col3 number);

Table created.

SQL> create index system.idx1 on system.test (col1,col2);

Index created.

SQL> alter table system.test ADD PRIMARY KEY (col1) using index

  2  /

Now the PK is validated using IDX1!

SQL>  select * from dba_constraints where table_name='TEST'  and owner='SYSTEM' and constraint_type='P'

  2  /

SYSTEM                         SYS_C00292332                  P TEST                                                                          ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED      GENERATED NAME          18.01.16 SYSTEM                         IDX1

and we only have 1 index!

SQL>  select count(*) from dba_indexes where table_name='TEST' and owner='SYSTEM'

  2  /

  COUNT(*)

----------

         1

So it seems that even though the index is not just on the column of interest (and in fact is not unique at all) oracle still quite happily uses it to enforce the constraint

The PK still works as we can see here

SQL>  insert into system.test values (1,1,1);

1 row created.

SQL> /

 insert into system.test values (1,1,1)

*

ERROR at line 1:

ORA-00001: Unique Constraint (SYSTEM.SYS_C00292348) verletzt

If the index order of idx1 is swapped to (col2,col1) then oracle still creates a new PK index - so it seems to only make use of another existing index if the first column of it corresponds to the PK definition.

The issue we have with datapump is that it is applying the PK after the first 2 indexes so re-using the existing 'available' index rather than creating a new one.

Quite confusing for a while - anyone know if this is actually a bug or expected behaviour?

I also noticed this strange bit of syntax which i never saw before when looking into this - anyone recognize this.....didn't realize you could actually specify create index within using index?

SQL> CREATE TABLE system.test(

     col1 number,

     col2 number,

     CONSTRAINT pk1 primary key(col1)

                    USING INDEX (create unique index idx1 on test (col1)));  

Table created.

Case solved anyway....... (oh and by the way this was 11.2.0.4 - i didn't try it on 12)