Unterbewertet oder unbekannt: Deferred Constraints

Über Nutzen und Nachteile von Constraints wird gerne und leidenschaftlich gestritten. Auf der Seite der Nachteile wird oft angeführt, daß die zeilenweise Verifizierung (im Englischen auch gerne als “slow-by-slow” verballhornt) ein Performance-Killer ist. Es wäre also durchaus praktisch, wenn auch Constraints stapelweise, z.B. pro Transaktion, abgearbeitet werden könnten. Auftritt der Deferred Constraints!

Deferrable Constraints sind schon seit Oracle 8 verfügbar. Sie erleichtern den Umgang mit Constraints durch folgende Eigenschaften:

  • Ein Deferred Constraint wird nicht sofort beim Insert oder Update geprüft sondern erst beim COMMIT. Dies bringt zum einen bei Massenänderungen Geschwindigkeitsvorteile gegenüber der sonst üblichen Prüfung Zeile für Zeile.

  • Zum anderen muss bei einer Beladung keine Rücksicht auf die Reihenfolge von Eltern-Kind-Tabellen genommen werden, wenn die Fremdschlüssel auf “deferred” gestellt sind.

  • Schlägt die Prüfung des Constraints fehl, findet ein implizites Rollback statt. Für Transaktionen mit deferred Constraints gilt also ein „alles oder nichts”-Prinzip.

  • Zweistufiges Prinzip: Das Constraint muss mit der Option „deferrable” angelegt sein. Mit der Option „initially [immediate | deferred]” wird dann festgelegt, wie das Constraint normalerweise behandelt wird.

  • Die Behandlung von deferrable Constraints kann auch auf Session-Ebene umgestellt werden, z.B.: set constraint all deferred.

 Wie immer sagt ein Beispiel mehr als 1000 Worte:

CREATE TABLE demo
(
  id NUMBER NOT NULL
, name VARCHAR2(50)
, typ VARCHAR2(1)
, CONSTRAINT demo_typ_cc CHECK ( typ IN ('a','b') )
);

INSERT INTO demo VALUES( 1, 'Asterix', 'a' );
INSERT INTO demo VALUES( 2, 'Oraculix', 'b' );

CREATE TABLE demo_typ
(
  typ VARCHAR2(1) NOT NULL
, detail VARCHAR2(50) NOT NULL
, CONSTRAINT typ_pk PRIMARY KEY ( typ )
);

INSERT INTO demo_typ VALUES( 'a', 'Gallier' );
INSERT INTO demo_typ VALUES( 'b', 'Informatiker' );

-- Und nun die Spezialität!
ALTER TABLE demo ADD
  CONSTRAINT dem_demtyp_fk FOREIGN KEY ( typ ) REFERENCES demo_typ ( typ )
  DEFERRABLE INITIALLY DEFERRED;

Das folgende UPDATE würde bei einem herkömmlichen Fremdschlüssel fehlschlagen. Da es aber erst nach dem COMMIT geprüft wird, können die Tabellen in beliebiger Reihenfolge geändert werden:


UPDATE demo SET typ = 'g' WHERE typ = 'a';

1 row updated.

UPDATE demo_typ SET typ = 'g' WHERE typ = 'a';

1 row updated.

COMMIT;

Commit complete.

Alles oder nichts!

Bei Datenübernahmen kann das “Alles oder nichts”-Prinzip zum Einsatz kommen: Ein COMMIT wird zu einem ROLLBACK, wenn die Constraint-Prüfung einen Fehler ergibt. So können Daten performant übernommen werden, wobei Fehlschläge dann die ganze Transaktion zurückrollen.

Caveats

Primary und Unique Key Constraints werden normalerweise durch Unique Indizes unterstützt. Nicht so im Fall von Deferrable Constraints: Da bei Änderungen an der Tabelle auch die Indizes unmittelbar mit geändert werden, müssen diese Indizes zwangsweise Non-Unique sein, damit vorübergehende Uneindeutigkeiten bis zum Ende der Transaktion überhaupt geduldet werden können.

Dies hat mehrere Implikationen:

  1. Non-Unique-Indizes werden zwangsläufig mit Range Scans durchsucht, da Unique Scans verständlicherweise nicht möglich sind. Ergo wird beim Durchsuchen mehr I/O generiert. Darüberhinaus werden mehr Latches benötigt. Beides wirkt sich auf die Performance aus. Dem gegenüber steht eine bessere Wartbarkeit von Non-Unique-Indizes, die hier aber nicht näher betrachtet werden soll.
  2. Der Optimizer kann sich nicht mehr darauf verlassen, daß ein Unique Constraint oder ein Primärschlüssel zu jedem Zeitpunkt konsistent ist. Ausführungspläne werden daher in der Annahme erzeugt, daß inkonsistente Daten vorliegen könnten. Damit sind o.g. Vorteile wie Table Elimination und Filterungen nicht mehr nutzbar.
  3. Der Optimizer kann sich ebenso nicht auf die Konsistenz von NOT NULL-Spalten verlassen, wenn disese mit einem Deferrable Constraint abgesichert sind. Dies verhindert Optimierungen, wie schon o.a.

Hier ein Beispiel für eine Abfrage, bei der der Optimizer normalerweise Table Elimination anwenden würde, weil eine der Tabellen gar nicht gebraucht wird:

 
SELECT name FROM demo NATURAL JOIN demo_typ;

---------------------------------------------
| Id | Operation              | Name | Rows |
---------------------------------------------
| 0 | SELECT STATEMENT        |        |  2 |
| 1 |  NESTED LOOPS           |        |  2 |
| 2 |   TABLE ACCESS FULL     | DEMO   |  2 |
|*3 |   INDEX UNIQUE SCAN     | TYP_PK |  1 |
---------------------------------------------
Die Tabelle “demo_typ” würde bei erfolgreich angewandter Table Elimination gar nicht im Ausführungsplan auftauchen. Da der Optimizer aber nicht “weiß”, daß es zwischen allen Zeilen in “demo” und “demo_typ” auch eine Entsprechung gibt, kann er Table Elimination nicht anwenden. Der Join und Scan auf “typ_pk” wird nun also völlig unnötig durchgeführt.

Fazit

Die Begeisterung für Deferrable Constraints ist nicht ungebremst: Einerseits können typische Nachteile bei Beladungen mit aktiven Constraints umgangen werden, insbesondere, wenn im stetigen Onlinebetrieb das Deaktivieren von Constraints keine Option ist. Andererseits werden wichtige Optimizer-Features beim späteren Verarbeiten der Daten ausgeschlossen.

Wie so oft, muss eine fallbezogene Abwägung getroffen werden: Lohnt der Gewinn durch das Verzögern von Constraints beim Schreiben den späteren Leistungsverlust beim Lesen?

Weblinks

  • Oracle 11gR2 DWH Guide: Integrity Constraints
  • Oracle 11gR2 Concepts: Deferrable Constraints
  • Potentielle Nachteile: Gute Erläuterung bei Richard Foote (dort mehr Links zum Thema). –> Deferred Constraints bauen auf Non-Unique Indizes auf, auch Unique Constraints! –> höherer Speicherbedarf, längere Laufzeiten.
  • Richard Foote beschreibt ferner, dass der Optimizer bei Deferred Constraints nicht mehr “weiß”, ob in einer NOT NULL-Spalte tatsächlich keine NULLs enthalten sind.
  • Randolf Geist hat in seinem Blog beispielhaft demonstriert, dass Parallel DML und Direct-Path-Inserts nicht funktionieren, wenn Deferred Constraints auf den betroffenen Tabellen existieren.
About these ads

Ein Gedanke zu „Unterbewertet oder unbekannt: Deferred Constraints

  1. Pingback: Anmerkungen zu INSERT ALL « Oraculix

Kommentar verfassen

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+ photo

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

Verbinde mit %s