Efficient Integrity Checks for SQL Constraints

1. Seminar „Intelligente Datenbanken“ Effiziente Integritätsprüfungfür SQL-Constraints Henning Lehmacher 12. Juli 2005

2. Übersicht • Teil 1: Einführung • Überprüfung von Constraints • Simulation durch Trigger • Constraints vs. Trigger • Teil 2: Soundcheck-Verfahren • Umwandlung: Constraints èeffiziente Trigger Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

3. Quellen • Hendrik Decker: Soundcheck for SQLI.V. Ramakrishnan (Ed.): PADL2001, LNCS 1990. Springer-Verlag, 2001 • Hendrik Decker: Integrity Enforcement on Deductive DatabasesLarry Kerschberg (Ed.): Expert Database Systems. The Benjamin/Cummings Publishing Company, 1987 • Andreas Behrend, Rainer Manthey, Birgit Pieper: An Amateur‘s Introduction to Integrity Constraints and Integrity Checking in SQLAndreas Heuer u.a. (Eds.): Datenbanksysteme in Büro, Technik und Wissenschaft (BTW), 9. GI-Fachtagung, Oldenburg, 7.-9. März 2001, Proceedings. Informatik Aktuell Springer 2001 Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

4. Teil I: Einführungsbeispiel pkw lkw Integritätsbedingung: Ein Kennzeichen ist nichtsowohl einem PKW als auch einem LKW zugeordnet Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

5. Assertions Erinnerung: Assertions Table Constraints Column Constraints Domain Constraints Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

6. Umwandlung in Assertion Ein Kennzeichen ist nichtsowohl einem PKW als auch einem LKW zugeordnet CREATE ASSERTION fahrz1 CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); Problem: Kein gängiges DBMS unterstützt Assertions / CHECK-Constraints über mehrere Tabellen. Wo liegen die Schwierigkeiten? Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

7. Überprüfung von Constraints CHECK - + CHECK • „Naive“ Handhabung durch ein DBMS lkw - pkw CREATE ASSERTION fahrz1 CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); Überprüfe bei jeder Änderung von pkw und lkw die CHECK-Bedingung (führe die SELECT-Anfrage aus)  O( |pkw| * |lkw| ) Vergleiche Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

8. Simulation durch Trigger Ein Kennzeichen ist nicht sowohl einem PKW als auch einem LKW zugeordnet CREATE TRIGGER ins_pkw_1 BEFORE INSERT ON pkwREFERENCING NEW AS insertedWHEN (EXISTS (SELECT * FROM lkw WHERE inserted.kennzeichen = lkw.kennzeichen))BEGIN ROLLBACK END; CREATE TRIGGER ins_lkw_1 BEFORE INSERT ON lkwREFERENCING NEW AS insertedWHEN (EXISTS (SELECT * FROM pkw WHERE inserted.kennzeichen = pkw.kennzeichen))BEGIN ROLLBACK END; ! Zusätzlich noch zwei Update-Trigger(von nun an nur noch Einfügen/Löschen berücksichtigt) DELETE-Trigger sind (in diesem Bsp.) nicht erforderlich! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

9. Performance Vergleiche beiEinfügeoperationenauf pkw oder lkw ... CREATE TRIGGER ins_pkw_1 BEFORE INSERT ON pkwREFERENCING NEW AS insertedWHEN (EXISTS (SELECT * FROM lkw WHERE inserted.kennzeichen = lkw.kennzeichen))BEGIN ROLLBACK END; CREATE TRIGGER ins_lkw_1 BEFORE INSERT ON lkwREFERENCING NEW AS insertedWHEN (EXISTS (SELECT * FROM pkw WHERE inserted.kennzeichen = pkw.kennzeichen))BEGIN ROLLBACK END; ... die neu eingefügten Zeilen mit der jeweils anderen Tabelle Im Gegensatz zu: O( |inserted| * |pkw| ) bzw.O( |inserted| * |lkw| ) Vergleiche bei Einfügungen Bei jeder Änderung O( |pkw| * |lkw| ) Vergleiche Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

10. Constraints vs. Trigger • CHECK-Constraints • rein deklarativ • Optimierungsarbeit liegt beim DBMS • hoher Optimierungsbedarf • intuitiver Optimierender Compiler • Trigger • eher imperativ • Optimierungsarbeit liegt beim Ersteller des SQL-Codes • wenig Optimierungs-möglichkeiten für DBMS • unübersichtlicher Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

11. Teil II: Das Soundcheck-Verfahren CHECK-Constraint • Differenz zwischen altem und neuem Zustand bestimmen • „Idle Updates“ überspringen • Relevante Integr.-Bedingungen bestimmen • Relevante Integr.-Bedingungen spezialisieren • Spezialisierte Integr.-Bedingungen optimieren Soundcheck-Verfahren Effizienter Trigger VI. Optimierten Trigger überprüfen Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

12. Phase 1 • Phase 1: Differenz zwischen altem und neuem Zustand bestimmen Differenztabelle inserted_pkw + deleted_pkw -- Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

13. Phase 1 • Bei Constraints über Sichten:Effektive Änderungen an den Sichten bestimmen • Wichtig für deduktive Datenbanken, würde hier jedoch zu weit führen  Annahme: Keine Constraints über Sichten! Constr. ! ! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

14. Phase 2 • Phase 2: „Idle Updates“ überspringen UPDATE pkw SET fabrikat = `Opel` WHERE kennzeichen = `BN-AA-1234`; kann nicht verletzt werden,da kennzeichen nicht verändert wird Ein Kennzeichen ist nicht sowohl einem PKW als auch einem LKW zugeordnet Allgemein: Überprüfung, ob ein Update eine „tatsächliche“ Änderung bewirkt,kann fast genauso aufwendig sein wie Überprüfung des Constraints selbst.  Updates / Doppeleinfügungen werden nicht gesondert betrachtet („Update := Löschen + Einfügen“) Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

15. Phase 3 • Phase 3: Relevante Integritätsbedingungen bestimmen inserted deleted Differenztabellen Welche Constraints können verletzt werden? ? ? Constraints Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

16. Phase 3 KonsistenterDB-Zustand • ... CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); -- Kann nichtverletzt werden ! auf jeden Fall konsistenter DB-Zustand! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

17. Phase 3 KonsistenterDB-Zustand Kann u.U.verletzt werden ! + ... CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); könnte Konsistenz verletzen Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

18. Phase 3 • Formal: NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen) Prädikatenlogik (Bereichskalkül) ¬∃ X [ pkw(X,_) ∧ lkw(X,_) ] Verschiebe Negationenmöglichst weit nach innen! Negation-innermost Form ∀ X [ ¬ pkw(X,_) ∨ ¬ lkw(X,_) ] atom. Prädikate negativer Polarität Atomare Prädikate sind von„negativer/positiver Polarität“wenn sie in der Negation-innermost Form negiert bzw. nicht negiert auftreten Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

19. Phase 3 • Eine Änderung ist dann relevant, wenn... • ... sie auf ein atomares Prädikat „matcht“ und ... • ... entgegengesetzte Polarität besitzt + t(k,a) -- v(n,p) X/k Substitution X/p ∀ X [ ¬ t(X,a) ∨ ¬ t(X,b) ] ∃ X [ ¬ u(X,m) ∨ v(n,X) ] --v(n,p) + t(k,a) Muss für alle atom. Prädikate untersucht werden!(Abbruch falls Matching gefunden wurde) Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

20. Phase 3 Wann ist die Integritätsbedingung verletzt? • Erstelle für jedes atom. Prädikat t(x1,...,xn, k1,...,km) der Constraint eine Formel… var. const. Auswertung falls t neg. Polarität hat falls t pos. Polarität hat oder Integr.-Bedingung Bsp.: • ∃ X‘ [ inserted_pkw(X‘,_) ∧ ¬ ∀ X [ ¬ pkw(X,_) ∨ ¬ lkw(X,_) ] ] • ∃ X‘ [ inserted_lkw(X‘,_) ∧ ¬ ∀ X [ ¬ pkw(X,_) ∨ ¬ lkw(X,_) ] ] Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

21. Phase 4 • Phase 4: Relevante Integr.-Bedingungen spezialisieren inserted_pkw + ... CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)) Spezialisierung ... CHECK (NOT EXISTS (SELECT * FROM inserted_pkw, lkw WHERE inserted_pkw.kennzeichen = lkw.kennzeichen)) Relevante Änderungvon pkw Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

22. Phase 4 deleted_pkw -- • Funktioniert das immer? ... CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)) Spezialisierung ? ... CHECK (EXISTS (SELECT * FROM deleted_pkw, lkw WHERE deleted_pkw.kennzeichen = lkw.kennzeichen)) OK Bei Existenzaussagen: Betrachtung ganzer Tabelle unvermeidlich! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

23. Phase 4 • Und hier? ... NOT EXISTS ( SELECT ... WHERE EXISTS (SELECT ... WHERE NOT EXISTS ... ) AND NOT EXISTS ( ... WHERE EXISTS ... ) ); Existenz- oder Universalaussage? Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

24. Phase 4 • Formal: • Eine Variable ist ∃-dominiert wenn links von ihr ein ∃ auftaucht. • ∀ X [ ∃ Y [¬p(X,Y) ∧ ∀ Z [ q(Y,Z) ] ] ] nicht∃-dominiert ∃-dominiert Variablen, die in der Negation-innermost Form nicht ∃-dominiert sind,können spezialisiert werden! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

25. Phase 4 • Wann ist die Integr.-Bedingung verletzt? • (Formel für atom. Prädikat mit neg. Polarität) • Phase 3: • ∃x1‘,...,xn‘ [inserted_t(x1‘,...,xn‘,k1,...,km) ∧ ¬IC ] Spezialisierte IC ohne ∃ oder ∀ vor den x1,...,xs Phase 4: ∃x1,...,xs [ ∃xs+1‘,...,xn‘ [inserted_t(x1,...,xs,xs+1‘,...,xn‘,k1,...,km) ] ∧ ¬ICS ] nicht∃-dominiert ∃-dominiert Constraint Bsp.: Phase 3:∃X‘[ inserted_pkw(X‘,_) ∧ ¬ ∀X [ ¬pkw(X,_) ∨ ¬lkw(X,_) ] ] Phase 4: ∃X [ inserted_pkw(X,_) ∧ ¬ ( ¬pkw(X,_) ∨ ¬lkw(X,_) ) ] Spezialisierte Constraint Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

26. Phase 4 Überprüfung in der Praxis: ∃X [ inserted_pkw(X,_) ∧ ( pkw(X,_) ∧ lkw(X,_) ) ] Lies Belegungen für Xaus inserted_pkw … … und prüfe, ob sie in pkw und lkw vorkommen ... EXISTS (SELECT * FROM inserted_pkw, pkw, lkw WHERE inserted_pkw.kennzeichen = pkw.kennzeichen AND inserted_pkw.kennzeichen = lkw.kennzeichen) Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

27. Phase 5 • Phase 5: Spezialisierte Integr.-Bedingungen optimieren ∃X [ inserted_pkw(X,_) ∧ pkw(X,_) ∧ lkw(X,_) ] ... EXISTS (SELECT * FROM inserted_pkw, pkw, lkw WHERE inserted_pkw.kennzeichen = pkw.kennzeichen AND inserted_pkw.kennzeichen = lkw.kennzeichen) nach Einfügen:inserted_pkw  pkw  Abfrage von pkw ist überflüssig ∃X [ inserted_pkw(X,_) ∧ lkw(X,_) ) ] ... EXISTS (SELECT * FROM inserted_pkw, lkw WHERE inserted_pkw.kennzeichen = lkw.kennzeichen) Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

28. Umwandlung in Trigger • Constraint ist verletzt wenn...: • ∃X [ inserted_pkw(X,_) ∧ lkw(X,_) ] • ∃X [ inserted_lkw(X,_) ∧ pkw(X,_) ] SQL • EXISTS (SELECT * FROM inserted_pkw, lkw WHERE inserted_pkw.kennzeichen = lkw.kennzeichen) • EXISTS (SELECT * FROM inserted_lkw, pkw WHERE inserted_lkw.kennzeichen = pkw.kennzeichen) Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

29. Umwandlung in Trigger • EXISTS (SELECT * FROM inserted_pkw, lkw WHERE inserted_pkw.kennzeichen = lkw.kennzeichen) • EXISTS (SELECT * FROM inserted_lkw, pkw WHERE inserted_lkw.kennzeichen = pkw.kennzeichen) inserted_pkw CREATE TRIGGER ins_pkw_1 BEFORE INSERT ON pkwREFERENCING NEW AS insertedWHEN (EXISTS (SELECT * FROM lkw WHERE inserted.kennzeichen = lkw.kennzeichen))BEGIN ROLLBACK END; CREATE TRIGGER ins_lkw_1 BEFORE INSERT ON lkwREFERENCING NEW AS insertedWHEN (EXISTS (SELECT * FROM pkw WHERE inserted.kennzeichen = pkw.kennzeichen))BEGIN ROLLBACK END; Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints

30. Zusammenfassung • Hoher Optimierungsbedarf bei Überprüfung von CHECK-Constraints • Constraints können durch Trigger simuliert werden • Soundcheck-Verfahren wandelt beliebige Constraints in effiziente Trigger um • Optimierungsmöglichkeiten (z.B.): • Bestimmung der Relevanz von Änderungen • Spezialisierung von Anfragen in der Constraint Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints