Von alten Käfern, abgeschalteten Constraints und nicht gelöschten Indizes

Hand auf’s Herz: Wer hält es auch für das normale Verhalten des Oracle RDBMS, daß das „disable“ eines Primary Key Constraints (PK) auch zur Löschung des zugehörigen Indizes führt?

Kürzlich erreichte mich eine Anfrage eines Kunden, der eine Beladung in einem Data Warehouse durchführen wollte, das gerade auf Oracle 10.2.0.4 migriert worden war.
Bei dem existierenden Belade-Skript trat ein bis dahin unüblicher Fehler auf: Vor der Beladung sollte das PK abgeschaltet werden (mit „disable constraint“). Dies führte bislang dazu, daß der zugehörige (unique) Index implizit gelöscht wurde.

Nun kam aber die Fehlermeldung „ORA-00001: unique constraint (…) violated„. Bei näherem Hinsehen stellte sich heraus, daß der zum Primärschlüssel gehörige Unique Index immer noch existierte und folgerichtig bei doppelten Datensätzen eine Fehlermeldung generiert wurde. Dies war jedoch nicht das bislang bekannte Verhalten. Das nunmehr aktuelle Verhalten möchte ich mit der folgenden, simplen Versuchsreihe demonstrieren, bei der sich zeigt, daß es einen Unterschied macht, ob der zum PK gehörige Index implizit oder explizit erzeugt wurde:

-------------------------------------------------------------------------------
-- Fall 1: Implizit erzeugter Index
drop table pktest
/
create table pktest( n integer )
/
alter table pktest add(
 CONSTRAINT PK_PKTEST
 PRIMARY KEY( n )
)
/
select index_name from user_indexes where table_name = 'PKTEST';
--> 0 rows
insert into pktest values(1)
/
insert into pktest values(1)
/
--> ORA-00001: unique constraint (XXX.PK_PKTEST) violated
alter table pktest DISABLE CONSTRAINT pk_pktest
/
select index_name from user_indexes where table_name = 'PKTEST';
--> no rows selected.
alter table pktest DROP CONSTRAINT pk_pktest
/
-------------------------------------------------------------------------------
-- Fall 2: Explizit erzeugter Index
create unique index pk_pktest on pktest(n)
/
alter table pktest add (
 constraint pk_pktest
 primary key(n)
 USING INDEX
)
/
alter table pktest DISABLE CONSTRAINT pk_pktest
/
select index_name from user_indexes where table_name = 'PKTEST';
-- INDEX_NAME                    
------------------------------
-- PK_PKTEST                     
-- 1 row selected.
insert into pktest values(1)
/
--> ORA-00001: unique constraint (XXX.PK_PKTEST) violated
alter table pktest DROP CONSTRAINT pk_pktest
/
drop index pk_pktest
/
-------------------------------------------------------------------------------
-- Fall 3: Implizit erzeugter Index mit "USING INDEX"-Klausel
alter table pktest add (
 constraint pk_pktest
 primary key(n)
 USING INDEX
)
/
alter table pktest DISABLE CONSTRAINT pk_pktest
/
select index_name from user_indexes where table_name = 'PKTEST';
--> no rows selected.
-------------------------------------------------------------------------------
-- FAZIT:
-- Das erwartete Verhalten beim Abschalten eines Constraints erreicht man, wenn
-- der Index implizit aufgebaut wird.

Nach eingehender Recherche stellte sich heraus, daß sich von Oracle 8.0 bis 9iR2 der Bug 1240495 hartnäckig gehalten hatte, der – nicht wie dokumentiert – zur Löschung explizit definierter Indizes führte. Erst in 10g wurde dieser Bug beseitigt, aber leider verließen sich unzählige Entwickler auf das eigentlich fehlerhafte Verhalten der Vorgängerversionen und bauten ETL-Skripte, die auf diesem Verhalten basierten.

Fragen über Fragen…

Was ist nun die gute Praxis, diesem Problem zu begegnen? Dies möchte ich in einem kurzen Frage-Antwort-Schema aufzeigen:

F: Wann wird der zugehörige Index nach dem Abschalten eines Constraints NICHT entfernt?
A: Nur, wenn es sich um einen benutzerdefinierten Index handelt, erzeugt nach dem Muster:
create index…, dann add constraint … using index.

F: Gibt es Probleme beim Einsatz bestimmter Tools?
A: Ja, z.B. erzeugt Oracle’s exp IMMER explizite Indizes. Hat man sich vorher auf die automatische Löschung eines implizit erzeugten Indizes verlassen können, so kann das Verhalten nach einem Import anders sein.

F: Welchen Workaround gibt es dagegen?
A: Den Einsatz der vollständigen Syntax beim Abschalten des Constraints:
„ALTER TABLE pktest DISABLE CONSTRAINT pk_pktest DROP INDEX;“

F: Was passiert, wenn ich das Constraint wieder anschalte?
A: Der Index wird IMPLIZIT wieder erzeugt. Wenn es vorher ein benutzerdefinierter Index mit besonderen Attributen war (Tablespace, Name, …), dann muss das mit ENABLE … USING INDEX angegeben werden.

F: Wie kann ich herausfinden, ob ein Index implizit oder explizit erzeugt wurde?
A: Mit folgender Abfrage, für die Leserechte auf das Data Dictionary nötig sind:

SELECT o.object_name
     , DECODE( bitand( property, 4096 ), 4096, 'implicit', 'user-generated' ) generation
  FROM SYS.ind$ i, user_objects o
 WHERE i.obj# = o.object_id
   AND object_type = 'INDEX'
   AND o.object_name = 'PK_PKTEST';

Anwendungsfall: PK mit nicht eindeutigem Index

F: „Wann sollte ich den Index überhaupt löschen und wann nicht?“
A: Eine Löschung ist eigentlich kein besonders „schöner“ Weg, die Performance einer Beladung zu beschleunigen. Oracle bietet für solche Fälle die Möglichkeit, Indizes in den Status „unusable“ zu stellen und diese Indizes bei Änderungen an der Tabelle zu überspringen:

ALTER INDEX nu_pktest UNUSABLE;
ALTER SESSION SET skip_unusable_indexes = TRUE;
INSERT INTO pktest ...

Vorteil: Der Index bleibt erhalten und kann mit REBUILD nach der Beladung wieder aufgebaut werden. Dieses Feature funktioniert aber nur für nicht eindeutige Indizes. Im Falle unseres PK mit Unique Index käme es zu dem Fehler „ORA-01502: index ‚PK_PKTEST‘ or partition of such index is in unusable state“.
Für so ein Szenario bietet es sich an, zunächst einen nicht eindeutigen (non-unique) Index und danach das Constraint mit USING INDEX anzulegen.  Ein komplettes Beladeverfahren sähe dann so aus:

CREATE TABLE pktest( n INTEGER )
/
CREATE INDEX nu_pktest ON pktest(n)
/
ALTER TABLE pktest ADD (
 CONSTRAINT pk_pktest
 PRIMARY KEY(n)
 USING INDEX nu_pktest
)
/
ALTER TABLE pktest DISABLE CONSTRAINT pk_pktest
/
ALTER INDEX nu_pktest UNUSABLE
/
ALTER SESSION SET skip_unusable_indexes = TRUE;
--------------------
-- Jetzt beladen
--------------------
ALTER INDEX nu_pktest REBUILD
/
ALTER TABLE pktest ENABLE CONSTRAINT pk_pktest
 EXCEPTIONS INTO exceptions
/

Weblinks

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s