Mittwoch, Mai 04, 2011

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.

Kommentare:

  1. Hallo,

    "Error: ORA-01502 occurs only on unusable unique index with SKIP_UNUSABLE_INDEXES=TRUE? [ID 272565.1]" führt genau den Testfall durch.

    Aus dem Dokument:
    1) You are inserting into a table having UNUSABLE UNIQUE index.
    You have set skip_unusable_indexes = TRUE, but you are still getting the following error:
    ORA-01502: index 'SYS.IDX_TEST1' or partition of such index is in unusable state
    2) The insert works fine if UNUSABLE index is BITMAP or NON-UNIQUE.


    Cause
    This is an expected behavior with UNIQUE index .
    Oracle cannot ignore unique constraint because it is required to enforce uniqueness.


    Viele Grüße,
    Andreas

    AntwortenLöschen
  2. Hallo Andreas,
    vielen Dank für den Hinweis auf das MOS-Dokument. So ganz einleuchtend finde ich Oracles Erklärung aber nicht: einen PK- oder UNIQUE-Constraint kann man ja problemlos vor dem Laden deaktivieren (und den zugehörgigen Index UNUSABLE setzen, sofern er nonunique ist) und erhält dann ggf. Fehlermeldungen, wenn der Constraint durch die geladenen Daten verletzt wird. Mir würde es eher einleuchten, wenn der Index sich auch so verhalten und die Fehler beim Rebuild liefern würde. Mir gefällt dabei nicht, dass man einen solchen Index offenbar tatsächlich droppen und anschließend (mit der vorher gesicherten DDL) neu aufbauen muss (sofern man nicht die erhöhte Laufzeit durch Index-Maintainance in Kauf nehmen will, was bei großen ETL-Operationen in der Regel keine gute Idee sein dürfte).

    Viele Grüße
    Martin

    AntwortenLöschen
  3. Nachtrag 24.05.2011: Ich habe Richard Foote in seinem Blog (http://richardfoote.wordpress.com/2011/02/27/oracle11g-zero-sized-unusable-indexes-part-ii-nathan-adler/#comment-14831) nach seiner Meinung befragt und seine Antwort lautete: "I think the 'logic' is that if a unique index were to be unsuable, then ignoring it would result in the violation of a business rule, that being suddenly having duplicate data when there should be none. You would get the same result even with a Non-Unique index, if the index was being used to police a PK or UK constraint." Einen (für mich) einleuchtenden technischen Grund gibt es demnach offenbar nicht.

    AntwortenLöschen