UNUSABLE Indexes

Dass es keine besonders gute Idee ist, Massendaten in eine Tabelle mit aktiven Indizes einzufügen, gehört zu den Dingen, die man bei der Definition von ETL-Prozessen im DWH-Kontext recht schnell herausfindet. Der APPEND-Hint kann diese Probleme zwar verringern, aber effektiver ist es, die Indizes vor dem Massendatenimport zu deaktivieren und nach dem Import neu aufzubauen (ein paar Testergebnisse dazu findet man hier).

Allerdings gibt es in diesem Zusammenhang ein paar Details, die man beachten muss. Dazu mal wieder ein Test (in 11.1.0.7). Zunächst lege ich eine harmlose Heap-Tabelle an und prüfe, wie sich diverse Index-Varianten verhalten:

create table test_nonpartitioned
( id number);

create index test_nonpartitioned_ix1 on test_nonpartitioned(id);
alter index test_nonpartitioned_ix1 unusable;

select index_name
     , status
  from user_indexes
 where index_name = upper('test_nonpartitioned_ix1');



INDEX_NAME                     STATUS
------------------------------ --------
TEST_NONPARTITIONED_IX1        UNUSABLE 

insert into test_nonpartitioned
select rownum id
  from dual
connect by level < 10000;

9999 Zeilen wurden erstellt.

Funktioniert also völlig problemlos. Jetzt der gleiche Versuch mit einem unique Index:

drop table test_nonpartitioned; 

create table test_nonpartitioned
( id number);

create unique index test_nonpartitioned_ix1 on test_nonpartitioned(id);
alter index test_nonpartitioned_ix1 unusable;

select index_name
     , status
  from user_indexes
 where index_name = upper('test_nonpartitioned_ix1');
INDEX_NAME                     STATUS
------------------------------ --------
TEST_NONPARTITIONED_IX1        UNUSABLE 

insert into test_nonpartitioned
select rownum id
  from dual
connect by level < 10000;     

insert into test_nonpartitioned
*
FEHLER in Zeile 1:
ORA-01502: Index 'DBADMIN.TEST_NONPARTITIONED_IX1' oder Partition dieses Index in nicht brauchbarem Zustand 

Wenn man im Netz nach Hinweisen zu ora-01502 sucht, findet man - vor allem in älteren Beiträgen - oft den Hinweis, den Parameter skip_unusable_indexes = true zu setzen, aber für 11.1.0.7 ist das ohnehin schon der Default-Wert. Offenbar kann man einen als unique definierten Index nicht einfach vor Massendaten-INSERTs deaktivieren und später wieder aktivieren. Tom Kyte verwies bei einer ähnlichen Frage auf die Dokumentation, die erklärt:

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.

In meinem Testfall ist allerdings überhaupt kein expliziter Constraint für die Tabelle definiert:

select * from user_constraints where table_name = 'TEST_NONPARTITIONED';

Es wurden keine Zeilen ausgewählt

Aber welche Rolle spielen Constraints? Man kann bekanntlich einen Primary Key auch durch einen non-unique index garantieren lassen:

drop table test_nonpartitioned; 

create table test_nonpartitioned
( id number);

create index test_nonpartitioned_ix1 on test_nonpartitioned(id);

alter table test_nonpartitioned add constraint test_nonpartitioned_pk primary key (id);

alter table test_nonpartitioned disable constraint test_nonpartitioned_pk;

alter index test_nonpartitioned_ix1 unusable;

select index_name
     , status
  from user_indexes
 where index_name = upper('test_nonpartitioned_ix1');

INDEX_NAME                     STATUS
------------------------------ --------
TEST_NONPARTITIONED_IX1        UNUSABLE 

insert into test_nonpartitioned
select rownum id
  from dual
connect by level < 10000;       

9999 Zeilen wurden erstellt. 

Demnach ist der (deaktivierte) Constraint kein Hindernis für die Deaktivierung des Index und das anschließende Insert.

Leider habe ich weder in der Doku noch im Netz eine kompakte Erklärung des Verhaltens gefunden, die weit über "es funktioniert auf diese Weise" hinausgeht - sollte jemand eine haben, würde ich mich über einen entsprechenden Kommentar freuen.