Vortrag
Werbung
Seminar „Intelligente Datenbanken“ Henning Lehmacher 12. Juli 2005 Effiziente Integritätsprüfung für SQL-Constraints Übersicht 2 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 Quellen 3 Hendrik Decker: Soundcheck for SQL I.V. Ramakrishnan (Ed.): PADL2001, LNCS 1990. Springer-Verlag, 2001 Hendrik Decker: Integrity Enforcement on Deductive Databases Larry 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 SQL Andreas 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 Teil I: Einführungsbeispiel 4 pkw lkw Kennzeichen Fabrikat Kennzeichen Achsen BN-AB-1234 VW SU-CD-5678 4 ... ... ... ... Integritätsbedingung: Ein Kennzeichen ist nicht sowohl einem PKW als auch einem LKW zugeordnet Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Assertions 5 Erinnerung: Assertions Table Constraints Column Constraints Domain Constraints Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Umwandlung in Assertion 6 Ein Kennzeichen ist nicht sowohl 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 Überprüfung von Constraints 7 + CHECK CHECK lkw pkw CREATE ASSERTION fahrz1 CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); „Naive“ Handhabung durch ein DBMS Ü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 Simulation durch Trigger 8 Ein Kennzeichen ist nicht sowohl einem PKW als auch einem LKW zugeordnet CREATE TRIGGER ins_pkw_1 BEFORE INSERT ON pkw REFERENCING NEW AS inserted WHEN (EXISTS (SELECT * FROM lkw WHERE inserted.kennzeichen = lkw.kennzeichen)) BEGIN ROLLBACK END; CREATE TRIGGER ins_lkw_1 BEFORE INSERT ON lkw REFERENCING NEW AS inserted WHEN (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 Performance 9 CREATE TRIGGER ins_pkw_1 BEFORE INSERT ON pkw Vergleiche bei Einfügeoperationen auf pkw oder lkw ... REFERENCING NEW AS inserted WHEN (EXISTS (SELECT * FROM lkw WHERE inserted.kennzeichen = lkw.kennzeichen)) BEGIN ROLLBACK END; ... die neu CREATE TRIGGER ins_lkw_1 BEFORE INSERT ON lkw eingefügten Zeilen mit der jeweils REFERENCING NEW AS inserted anderen Tabelle WHEN (EXISTS (SELECT * FROM pkw WHERE inserted.kennzeichen = pkw.kennzeichen)) BEGIN ROLLBACK END; Im Gegensatz zu: O( |inserted| * |pkw| ) bzw. O( |inserted| * |lkw| ) Vergleiche bei Einfügungen Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Bei jeder Änderung O( |pkw| * |lkw| ) Vergleiche Constraints vs. Trigger 10 Optimierender Compiler CHECK-Constraints Trigger rein deklarativ eher imperativ Optimierungsarbeit liegt beim DBMS hoher Optimierungsbedarf intuitiver Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Optimierungsarbeit liegt beim Ersteller des SQL-Codes wenig Optimierungsmöglichkeiten für DBMS unübersichtlicher Teil II: Das Soundcheck-Verfahren 11 CHECK-Constraint Soundcheck-Verfahren I. Differenz zwischen altem und neuem Zustand bestimmen II. „Idle Updates“ überspringen III. Relevante Integr.-Bedingungen bestimmen IV. Relevante Integr.-Bedingungen spezialisieren V. Spezialisierte Integr.-Bedingungen optimieren Effizienter Trigger VI. Optimierten Trigger überprüfen Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Phase 1 12 Phase 1: Differenz zwischen altem und neuem Zustand bestimmen Kennzeichen Fabrikat BN-AB-1234 VW K-VB-1000 Ford BM-W-999 BMW SU-RF-4545 Fiat Kennzeichen Fabrikat BN-AB-1234 VW K-VB-1000 Ford BM-W-999 BMW SU-RF-4545 Fiat Differenztabelle inserted_pkw + BM-W-999 BMW SU-RF-4545 Fiat deleted_pkw -- Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints K-VB-1000 Ford BM-W-999 BMW Phase 1 13 Constr. ! ! 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! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Phase 2 14 Phase 2: „Idle Updates“ überspringen UPDATE pkw SET fabrikat = `Opel` WHERE kennzeichen = `BN-AA-1234`; Ein Kennzeichen ist nicht sowohl einem PKW als auch einem LKW zugeordnet kann nicht verletzt werden, da kennzeichen nicht verändert wird 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 Phase 3 15 Phase 3: Relevante Integritätsbedingungen bestimmen inserted deleted Differenztabellen ? ? Constraints Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Welche Constraints können verletzt werden? Phase 3 16 -- Kennzeichen Fabrikat Kennzeichen Achsen BN-D-007 VW K-VB-112 4 ... ... ... ... BM-W-999 BMW K-UH-123 BMW Konsistenter DB-Zustand Kann nicht verletzt werden ! ... CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); Kennzeichen Fabrikat Kennzeichen Achsen BN-D-007 VW K-VB-112 4 ... ... ... ... auf jeden Fall konsistenter DB-Zustand! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Phase 3 17 Kennzeichen Fabrikat Kennzeichen Achsen BN-D-007 VW K-VB-112 4 ... ... ... ... + BM-W-999 BMW K-UH-123 BMW Konsistenter DB-Zustand Kann u.U. verletzt werden ! ... CHECK (NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen)); Kennzeichen Fabrikat Kennzeichen Achsen BN-D-007 VW K-VB-112 4 ... ... ... ... BM-W-999 BMW K-UH-123 BMW Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints könnte Konsistenz verletzen Phase 3 18 Formal: NOT EXISTS (SELECT * FROM pkw, lkw WHERE pkw.kennzeichen = lkw.kennzeichen) Prädikatenlogik (Bereichskalkül) ¬∃ X [ pkw(X,_) ∧ lkw(X,_) ] Negation-innermost Form Verschiebe Negationen möglichst weit nach innen! ∀ 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 Phase 3 19 Eine Änderung ist dann relevant, wenn... ... sie auf ein atomares Prädikat „matcht“ und ... ... entgegengesetzte Polarität besitzt + t(k,a) -- v(n,p) Substitution X/k ∀ X [ ¬ t(X,a) ∨ ¬ t(X,b) ] X/p ∃ X [ ¬ u(X,m) ∨ v(n,X) ] + 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 -- v(n,p) Phase 3 20 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 oder x1' ,..., x n ' [inserted_ t(x 1' ,..., x n ' , k1,..., k m ) IC] falls t neg. Polarität hat x1' ,..., x n ' [deleted_t (x1' ,..., x n ' , k1,..., k m ) IC] falls t pos. Polarität hat 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 Phase 4 21 Phase 4: Relevante Integr.-Bedingungen spezialisieren inserted_pkw + BN-AA-1111 Volvo ... ... ... 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 Änderung von pkw Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Phase 4 22 Funktioniert das immer? deleted_pkw -- BN-AA-1111 Volvo ... 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)) Kennzeichen Fabrikat K-VB-110 VW BN-AA-1111 Volvo OK Kennzeichen Achsen K-VB-110 4 BN-AA-1111 3 Bei Existenzaussagen: Betrachtung ganzer Tabelle unvermeidlich! Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Phase 4 23 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 Phase 4 24 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 Phase 4 25 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 Phase 4 26 Überprüfung in der Praxis: ∃X [ inserted_pkw(X,_) ∧ ( pkw(X,_) ∧ lkw(X,_) ) ] Lies Belegungen für X aus 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 Phase 5 27 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 Umwandlung in Trigger 28 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 pkw REFERENCING NEW AS inserted WHEN (EXISTS (SELECT * FROM lkw WHERE inserted.kennzeichen = lkw.kennzeichen)) BEGIN ROLLBACK END; CREATE TRIGGER ins_lkw_1 BEFORE INSERT ON lkw REFERENCING NEW AS inserted WHEN (EXISTS (SELECT * FROM pkw WHERE inserted.kennzeichen = pkw.kennzeichen)) BEGIN ROLLBACK END; Henning Lehmacher - Effiziente Integritätsprüfung für SQL-Constraints Zusammenfassung 30 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
