Universitt Karlsruhe TH Kapitel 10 Anfragebearbeitung 2009 Univ

Universität Karlsruhe (TH) Kapitel 10 Anfragebearbeitung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI

Gegenstand des Kapitels Datenmodell Datentypen: Satzmengen Operatoren: Operatoren auf Mengen Datentypen: Sätze und Satzmengen Operatoren: Operatoren auf Sätzen Datentypen: phys. Zugriffsstrukturen auf Sätze Operatoren: seq. Durchlauf, gezielte Suche Transparenter homogener Speicher Datentypen: Seite = feste Anzahl von Bytes Segment = var. Anzahl von Seiten Operatoren: Anforderung/Freigabe von Seiten Segmente anlegen/öffnen/schließen Datentypen: Block = feste Anzahl von Bytes Datei = variable Anzahl v. Blöcken Operatoren: Dateien anlegen/öffnen/schließen Lesen/Schreiben von Blöcken 2 Performanz Mengenorientiertes Datenmodell Anfragebearbeitung Optimaler Einsatz der logischen Ressourcen Satzorientiertes Datenmodell Satz- u. Satzmengenverwaltung Vorschau auf zukünftig benötigte Daten Satzzugriffsstrukturen Zugriffsschicht Vermeiden nicht aktuell benötigter Daten Hauptspeicherseiten u. Segmente Segment- u. Pufferverwaltung Bevorratung von Daten im Hauptspeicher (rechtzeitige Bereitstellung vor Benutzung) Dateien Dateiverwaltung Geräteschnittstelle Schneller Transport zwischen Haupt- und Hintergrundspeicher Speicherstruktur Geräte-E/A © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

3 Kapitel 10. 1 Vorgehensmodell © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Ziel n Sehr hohes Abstraktionsniveau der mengenorientierten Schnittstelle (SQL). u n 4 Anfragen sind deklarativ (nicht-prozedural), d. h. es wird spezifiziert, was man finden möchte, aber nicht wie. Chance und Herausforderung: Das wie bestimmt sich aus der Abbildung der Anfrage auf die Operatoren der internen Ebene. u Zu einem was kann es zahlreiche wie‘s geben: Verschiedene Algorithmen (Kapitel 9), die auf unterschiedlichen Abbildungen der Relationen auf interne Dateien und logische Zugriffspfade beruhen. Ø Reihenfolge der Operatoren, um hohe Selektivität zu erreichen. Ø n Effiziente Anfrageauswertung durch Anfrageoptimierung. u Ziel: Gesucht wird nicht die optimale Auswertungsstrategie, sondern eine einigermaßen effiziente Variante (z. B. „avoiding the worst case“). © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Ablauf der Anfragebearbeitung Deklarative Anfrage 5 AnfrageÜbersetzer Sichtauflösung Algebraischer Ausdruck Anfrage. Optimierer Auswertungs. Plan (QEP) Codeerzeugung Ausführung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Verfeinerter Ablauf der Anfragebearbeitung Übersetzungszeit deklarative SQL-Anfrage Scanner, Parser, Sichtauflösung Anfrage in interner Form Ergebnis Code-Erzeugung Logische Optimierung algebraischer Ausdruck auf internen Dateien QEP Physische Optimierung Planparametrisierung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 6 Ausführung DBSLaufzeitsystem Standardisierung Kostenbasierte Auswahl Laufzeit QEP Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Verfeinerter Ablauf der Anfragebearbeitung deklarative SQL-Anfrage Scanner, Parser, Sichtauflösung Anfrage in interner Form Standardisierung Logische Optimierung algebraischer Ausdruck auf internen Dateien Physische Optimierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Ergebnis 7 Traditionelle Übersetzung, Ausführung DBSErgebnis. Laufzeitsystem ist ein standardisierter algebraischer. Code Ausdruck auf Relationen und mit QEP Annahme NSM damit auch Code-Erzeugung auf internen Dateien QEP Planparametrisierung Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Verfeinerter Ablauf der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Standardisierung Logische Optimierung algebraischer Ausdruck auf internen Dateien Physische Optimierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 8 Code-Erzeugung QEP Umformung zunächst QEP unabhängig und dann abhängig. Planparametrivon sierung Zugriffstrukturen, dann Bewertung der Pläne. Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Verfeinerter Ablauf der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Standardisierung algebraischer Ausdruck Logische Bei Standardanfragen wird das auf internen Dateien Optimierung Ergebnis von Übersetzung und Physische Optimierung gespeichert. Optimierung Bei Aufruf Optimierung muss dann der QEP nur noch Kostenbasiergeeignet parametrisiert werden. te Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 9 Code-Erzeugung QEP Planparametrisierung Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Verfeinerter Ablauf der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Sofern QEP nicht vom Laufzeitsystem interpretiert Standardisierung wird, muss er in ausführbaren übersetzt. Ausdruck werden. Logische Code algebraischer Code-Erzeugung Optimierung auf internen Dateien QEP Physische Optimierung Planparametrisierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 10 QEP Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

11 Kapitel 10. 2 Anfrageübersetzung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Stellung in der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Code Standardisierung Code-Erzeugung Logische Optimierung algebraischer Ausdruck auf internen Dateien QEP Physische Optimierung Planparametrisierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 12 QEP Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Grundsätze Prinzipielles Vorgehen zur Umsetzung deklarativ imperativ: n Definiere Algebra von Operatoren, die einzelne Algorithmen kapseln und als Basis-Bausteines imperativen Programms dienen. u n n 13 Als Operator-Algebra dient (zunächst) die relationale Algebra. SQL-Ausdrücke werden zunächst standardisiert (in bestimmte Standardformen überführt). Für die Standardformen erfolgt Übersetzung anhand von Syntaxbaum. Imperative Programme sind dann Bäume von Operatoren. u Ordne jeder Grammatikregel der deklarativen Sprache eine Übersetzungsregel zu, die besagt, wie der äquivalente Operatorbaum zu konstruieren ist. u n Operatorbaum ist Eingabe für die Optimierungsphase. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

14 Kapitel 10. 2. 1 Standardisierung der SQL-Anfrage © 2009 Univ, Karlsruhe, IPD, Prof.

Bestandteile der Übersetzung 15 Externes Datenmodell relationaler DBMS bietet: n Relation als fundamentalen Datentyp, n Sichten, n Übersetzung von SQL-Anfragen n Konsistenzbedingungen, in interne Operator-Ausdrücke n Schutzmechanismen, auf Relationen. n Anfragesprache SQL. n n n Behandlung von Sichten durch Query modification: Definition wird in Anfrage eingearbeitet. Überwachung von Konsistenzbedingungen (ggf. zusätzliche Operatoraufrufe) bei Änderungen, gelegentlich Einbau in die Anfrage zur Nutzung bei der Anfragebearbeitung. Durchsetzung der Schutzmechanismen durch RechteÜberprüfung bei Übersetzung der Anfrage. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Grundmuster der SQL-Übersetzung 16 Select-Ausdruck select A 1, A 2, . . . , An from R 1, R 2, . . . , Rm where B wird überführt in: A 1, A 2, . . . , An ( B (R 1 R 2 . . . Rm)). Der Select-Ausdruck muss dazu gewissen Standards genügen, die wir nachfolgend entwickeln Schrittweise Transformation der ursprünglichen Anfrage. Wir illustrieren die Vorgehensweise direkt an der SQL-Anfrage. Üblicherweise wird die Transformation an Ausdrücken einer (meist baumartigen) Zwischensprache vorgenommen. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Transformation (1) 17 Beseitigen konjunktiver Bedingungen: select A 1, A 2, . . . , An from R 1, . . . , Rm where B mit B B 1 . . . Bk (B in disjunktiver Normalform). Ersetze Anfrage durch (select A 1, A 2, . . . , An from R 1, . . . , Rm where B 1) union (select A 1, A 2, . . . , An from R 1, . . . , Rm where B 2) union. . . union (select A 1, A 2, . . . , An from R 1, . . . , Rm where Bk). © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Transformation (2) Select-Ausdruck select A 1, A 2, . . . , An from R 1, R 2, . . . , Rm where B wird überführt in: A 1, A 2, . . . , An ( B (R 1 R 2 . . . Rm)). 18 Probleme: n B kann geschachtelte Anfragen enthalten. n Ri kann ein Tabellenausdruck sein, der von R 1, . . . , Ri-1 abhängt. n group by und having-Klauseln müssen berücksichtigt werden. n A 1, A 2, . . . , An können Aggregatfunktionen sein. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung geschachtelter Anfragen (1) 19 Rückführung von in/not in auf any bzw. all: Ersetze u x in (select A from R 1, . . . , Rm where B) durch u x =any (select A from R 1, . . . , Rm where B). Ersetze u x not in (select A from R 1, . . . , Rm where B) durch u x <>all (select A from R 1, . . . , Rm where B). © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung geschachtelter Anfragen (2) 20 Rückführung von any und all auf exists bzw. not exists: Ersetze u x any (select A from R 1, . . . , Rm where B) durch u exists (select from R 1, . . . , Rm where B and x A). Ersetze u x all (select A from R 1, . . . , Rm where B) durch u not exists (select from R 1, . . . , Rm where B and not x A). © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung geschachtelter Anfragen (3) 21 Rückführung von not exists auf exists: Ersetze u select A 1, A 2, . . . , An from R 1, . . . , Rm where B and not exists (T) durch u select from A 1, A 2, . . . , An ((select from R 1, . . . , Rm where B) except (select from R 1, . . . , Rm where B and exists (T))) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung geschachtelter Anfragen (4) 22 Elimination von exists: Ersetze u select from where and A 1, A 2, . . . , An R 1, . . . , Rm B exists (select from Rm+1, . . . , Rk where B') durch u select A 1, A 2, . . . , An from R 1, . . . , Rm+1, . . . , Rk where B and B'. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Transformation (3) Select-Ausdruck select A 1, A 2, . . . , An from R 1, R 2, . . . , Rm where B wird überführt in: A 1, A 2, . . . , An ( B (R 1 R 2 . . . Rm)). 23 Probleme: n B kann geschachtelte Anfragen enthalten. n Ri kann ein Tabellenausdruck sein, der von R 1, . . . , Ri-1 abhängt. n group by und having-Klauseln müssen berücksichtigt werden. n A 1, A 2, . . . , An können Aggregatfunktionen sein. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Allgemeiner Tabellenausdruck im Ergebnis 24 Standardisiere select auf Attribute: Ersetze u allgemeinen Tabellenausdruck T (z. B. select mit group by/having, join, values etc. ) durch u select from (T) as R(A 1, A 2, . . . , An) , wobei R ein frei gewählter, sonst nirgendwo vorkommender Name ist und A 1, A 2, . . . , An Namen für die Spalten von T sind. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (1) 25 FLUGZEUGTYP(Ftyp. Id, Name, Cockpit. Crew, First, Business, Economy) FLUG(Flug. Nr, Ftyp. Id, von, nach, Entfernung, Abflugzeit, Ankunftszeit, Tage) BUCHUNG(Flug. Nr, Ticket. Nr, Platz. Nr, Datum) „Suche nach Flügen, die an irgendeinem Tag überbucht sind“: select from where and Flug. Nr FLUG F, FLUGZEUGTYP FT F. Ftyp. Id = FT. Ftyp. Id FT. First + FT. Business + FT. Economy <any (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (2) 26 select Flug. Nr from FLUG F, FLUGZEUGTYP FT where F. Ftyp. Id = FT. Ftyp. Id and FT. First + FT. Business + FT. Economy <any (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) Allg. Tabellenausdruck T im Ergebnis select from (T) as R(A 1, A 2, . . . , An) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (3) 27 select Flug. Nr from FLUG F, FLUGZEUGTYP FT where F. Ftyp. Id = FT. Ftyp. Id and FT. First + FT. Business + FT. Economy <any (select from (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) as T(count)) Rückführung von any auf exists: x any (select A from R 1, . . . , Rm where B) exists (select from R 1, . . . , Rm where B and x A) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (4) 28 select Flug. Nr from FLUG F, FLUGZEUGTYP FT where F. Ftyp. Id = FT. Ftyp. Id and exists (select from (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) as T(count) FT. First+FT. Business+FT. Economy < T. count) where Beseitigen von exists: from R 1, . . . , Rm, Rm+1, . . . , Rk where B and B' and exists (select from Rm+1, . . . , Rk where B') © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (5) 29 select Flug. Nr from FLUG F, FLUGZEUGTYP FT, (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) as T(count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < T. count © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Transformation (4) Select-Ausdruck select A 1, A 2, . . . , An from R 1, R 2, . . . , Rm where B wird überführt in: A 1, A 2, . . . , An ( B (R 1 R 2 . . . Rm)). 30 Probleme: n B kann geschachtelte Anfragen enthalten. n Ri kann ein Tabellenausdruck sein, der von R 1, . . . , Ri-1 abhängt. n group by und having-Klauseln müssen berücksichtigt werden. n A 1, A 2, . . . , An können Aggregatfunktionen sein. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung von from-Abhängigkeiten (1) 31 Komplikation: n Tabellenausdrücke in from-Klausel können von weiter links stehenden Tabellenausdrücken abhängen. u In diesem Fall kann from-Klausel nicht direkt in kart. Produkt übersetzt werden. from FLUG F, FLUGZEUGTYP FT, (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) as T(count) n Dann sukzessives Eliminieren von Abhängigkeiten wie bei geschachtelten Anfragen. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung von from-Abhängigkeiten (2) 32 Herausfaktorisieren von relationalen Operatoren: Ersetze u from R 1, . . . , Rm, (T 1 union T 2) durch u from ((select from R 1, . . . , Rm, T 1) union (select from R 1, . . . , Rm, T 2)) , und analog für intersect, except, join. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung von from-Abhängigkeiten (3) 33 Herausfaktorisieren von select: Ersetze u from R 1, . . . , Rm, (select from where E 1, . . . , En Rm+1, . . . , Rk B) (select from where R 1. , . . . , Rm. , E 1, . . . , En R 1, . . . , Rm+1, . . . , Rk B) durch u from (ggf. mit Auflösung von Namenskonflikten). © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Behandlung von from-Abhängigkeiten (4) 34 Ebenso mit Gruppierung: Ersetze u from R 1, . . . , Rm, (select from where group by having E 1, . . . , En Rm+1, . . . , Rk B En+1, . . . , El B') (select from where group by having R 1. , . . . , Rm. , E 1, . . . , En R 1, . . . , Rm+1, . . . , Rk B R 1. , . . . , Rm. , En+1, . . . , El B') durch u from © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (6) 35 select Flug. Nr from FLUG F, FLUGZEUGTYP FT, (select count (Ticket. Nr) from BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by Datum) as T(count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < T. count Herausfaktorisieren von select mit group by © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (7) 36 select Flug. Nr from (select F. , FT. , count (Ticket. Nr) from FLUG F, FLUGZEUGTYP FT, BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by F. , FT. , Datum) as DFT(F. , FT. , count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count DFT(F. , FT. , count) ist Abkürzung. Genauer: Anstelle von F. * und FT. * hat man sich die gesamte Folge der Attribute vorzustellen, DFT hat also 15 Attribute. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Fazit: Standardisiertes Ergebnis 37 Sukzessive Anwendung vorhergehender Regeln führt schließlich auf einfache Anfragen, d. h. Anfragen der Form: n einzelne Basistabelle, n einzelnes ungeschachteltes select-Statement (evtl. mit Gruppierung), dessen from-Klausel wieder eine einfache Anfrage ist, n relationale Verknüpfung (union, intersect, except, join) einfacher Anfragen. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

38 Kapitel 10. 2. 2 Übersetzung in Standardform © 2009 Univ, Karlsruhe, IPD, Prof.

Übersetzungsregeln select. A 1, A 2, . . . , An from R 1, R 2, . . . , Rm where B 39 A 1, A 2, . . . , An ( B (R 1 R 2 . . . Rm)) Basistabellen werden durch Referenzen auf entsprechende Datenstrukturen ersetzt. Folge einfacher Anfragen in der from-Klausel führt auf kartesisches Produkt. select-Anweisung (evt. mit Gruppierung) wird in Kombination aus Selektions- und Projektionsoperator auf die from-Klausel übersetzt. Gruppierung wird in Operator der erweiterten Algebra übersetzt. Relationale Verknüpfung (union, intersect, except, join) einfacher Anfragen wird in entsprechende relationale Operatoren übersetzt. R 1 © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann A 1, A 2, . . . , An B Rm R 3 R 2 DBI 10

Beispiel (8) 40 select Flug. Nr from (select F. , FT. , count (Ticket. Nr) from FLUG F, FLUGZEUGTYP FT, BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by F. , FT. , Datum) as DFT(F. , FT. , count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count Folge einfacher Anfragen in der inneren from-Klausel © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (9) 41 select Flug. Nr from (select F. , FT. , count (Ticket. Nr) from FLUG F FLUGZEUGTYP FT BUCHUNG B where B. Flug. Nr = F. Flug. Nr group by F. , FT. , Datum) as DFT(F. , FT. , count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count Innere where-Klausel © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (10) 42 select Flug. Nr from (select F. , FT. , count (Ticket. Nr) from B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B) group by F. , FT. , Datum) as DFT(F. , FT. , count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count Gruppierung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (11) 43 select Flug. Nr from (select F. , FT. , count (Ticket. Nr) from F. , FT. , Datum ( B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B)) ) as DFT(F , FT , count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count Innere select-Klausel © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (12) 44 select Flug. Nr from ( F. , FT. , count (Ticket. Nr) ( F. , FT. , Datum ( B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B)) )) as DFT(F , FT , count) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count Übersetzung der as-Klausel durch Rename-Operator auf die DFT-Attribute © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (13) 45 select Flug. Nr from F. , FT. , count (Ticket. Nr) ( F. , FT. , Datum ( B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B)))) where F. Ftyp. Id = FT. Ftyp. Id and FT. First+FT. Business+FT. Economy < DFT. count Verbleibende where-Klausel © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (14) 46 select Flug. Nr from F. Ftyp. Id = FT. Ftyp. Id FT. First+FT. Business+FT. Economy < DFT. count ( F. , FT. , count (Ticket. Nr) ( F. , FT. , Datum ( B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B))))) Streng genommen stehen hier die umbenannten DFT-Attribute Verbleibende select-Klausel © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (15) 47 F. Flug. Nr ( F. Ftyp. Id = FT. Ftyp. Id FT. First+FT. Business+FT. Economy < count ( F. , FT. , count (Ticket. Nr) ( F. , FT. , Datum ( B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B)))))) Streng genommen steht hier das umbenannte DFT-Attribut © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel (16) 48 Endergebnis als Operatorbaum: F. Flug. Nr ( F. Ftyp. Id = FT. Ftyp. Id FT. First+FT. Business+FT. Economy < count ( F. , FT. , count (Ticket. Nr) ( F. , FT. , Datum ( B. Flug. Nr = F. Flug. Nr (FLUG F FLUGZEUGTYP FT BUCHUNG B)))))) F. Flug. Nr F. Ftyp. Id = FT. Ftyp. Id FT. First+FT. Business+FT. Economy < count F. , FT. , count (Ticket. Nr) F. , FT. , Datum B. Flug. Nr = F. Flug. Nr FLUG F © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann FLUGZEUGTYP FT BUCHUNG B DBI 10

49 Kapitel 10. 3 Logische Anfrageoptimierung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI

Stellung in der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Code Standardisierung Code-Erzeugung Logische Optimierung algebraischer Ausdruck auf internen Dateien QEP Physische Optimierung Planparametrisierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 50 QEP Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Grundgedanke 51 Anordnung der Operatoren gemäß n Ziel: „Optimizing the average case“. n Hauptregel: „Halte die Zwischenergebnisse so klein wie möglich. “ u Regel kann im Einzelfall auch nachteilig sein. Umsetzung der Hauptregel n , vor ⋈, , , … ausführen, denn u und verringern den Umfang, u ⋈, … “blähen“ oft die Zwischenrelationen auf. Systematische Vorgehensweise: n Anwendung algebraischer Äquivalenzen. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Algebraische Äquivalenzen (1) 52 1. Konjunktionen im Selektionsprädikat c 1 c 2 . . . cn (R) c 1( c 2(…( cn(R)) …)) 2. ist kommutativ c 1( c 2((R)) c 2( c 1((R)) 3. -Kaskaden: Falls L 1 L 2 … Ln, dann gilt L 1( L 2 (…( Ln(R)) …)) L 1(R) 4. Vertauschen von und Falls die Selektion sich nur auf Attribute A 1, …, An der Projektionsliste bezieht, können die beiden Operationen vertauscht werden c( A 1, …, An(R)) A 1, …, An( c(R)) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Algebraische Äquivalenzen (2) 53 5. Die Operationen , ⋈, und sind kommutativ 6. Ein kartesisches Produkt, das von einer Selektions. Operation gefolgt wird, deren Selektionsprädikat Attribute aus beiden Operanden des kartesischen Produktes enthält, kann in eine Joinoperation umgeformt werden. Sei c eine Bedingung der Form A B, mit A ein Attribut von R und B ein Attribut aus S. c(R S) R ⋈c S © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Algebraische Äquivalenzen (3) 7. Vertauschen von mit ⋈ oder 54 Falls das Selektionsprädikat c nur auf Attribute der Relation R zugreift, kann man die beiden Operationen vertauschen: c(R ⋈p S) c(R) ⋈p S c(R S) c(R) S Falls das Selektionsprädikat c eine Konjunktion der Form „c 1 c 2“ ist und c 1 sich nur auf Attribute aus R und c 2 sich nur auf Attribute aus S bezieht, gilt folgende Äquivalenz: c(R ⋈p S) c 1(R) ⋈p c 2(S) c(R S) c 1(R) c 2(S) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Algebraische Äquivalenzen (4) 8. Vertauschung von mit ⋈ Die Projektionsliste L sei: L = {A 1, …, An, B 1, …, Bm}, wobei Ai Attribute aus R und Bi Attribute aus S seien. Falls sich das Joinprädikat c nur auf Attribute aus L bezieht, gilt folgende Umformung: L(R ⋈c S) ( A 1, …, An(R)) ⋈c ( B 1, …, Bn(S)) Falls das Joinprädikat sich auf weitere Attribute, sagen wir A 1', …, Ap', aus R und B 1', …, Bq' aus S bezieht, müssen diese für die Join-Operation erhalten bleiben und können erst danach herausprojiziert werden: L(R ⋈c S) A 1', …, Ap', B 1', …, Bq'(( A 1, …, An, A 1', …, Ap'(R)) ⋈c ( B 1, …, Bn, B 1', …, Bq'(S))) Für die -Operation gibt es kein Prädikat, so dass die Einschränkung entfällt. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 55 DBI 10

Algebraische Äquivalenzen (5) 9. Die Operationen ⋈, , , sind jeweils assoziativ. Wenn also eine dieser Operationen bezeichnet, so gilt: (R S) T R (S T) 10. Die Operation ist distributiv mit , , . Falls eine dieser Operationen bezeichnet, gilt: c(R S) ( c(R)) ( c(S)) 11. Die Operation ist distributiv mit , , . Falls eine dieser Operationen bezeichnet, gilt: L(R S) ( L(R)) ( L(S)) 12. Die Join- und Selektionsprädikate können mittels de Morgan's Regeln umgeformt werden: (c 1 c 2) ( c 1) ( c 2) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 56 DBI 10

Anwendung als Transformationsregeln c 1 c 2 . . . cn (R) c 1( c 2(…( cn(R)) …)) c 1( c 2((R)) c 2( c 1((R)) L 1( L 2 (…( Ln(R)) …)) L 1(R) c( A 1, …, An(R)) A 1, …, An( c(R)) R S S R (⋈, , , ) c(R S) R ⋈c S c(R S) c(R) S (⋈, ) Zerlegen/Zusammenfassen in Kaskaden von -Operationen 57 Selektionsoperationen soweit „nach unten“ (so früh) wie möglich Beseitigen von (zu ⋈) Projektionsoperationen soweit „nach unten“ wie möglich c(R S) c 1(R) c 2(S) (⋈, ) L(R ⋈c S) ( A 1, …, An(R)) ⋈c ( B 1, …, Bn(S)) L(R ⋈c S) L(( A 1, …, An, A 1', …, An'(R)) ⋈c ( B 1, …, Bn, B 1', …, Bn'(S))) (R S) T R (S T) (⋈, , , ) c(R S) ( c(R)) ( c(S)) ( , , ) L(R S) ( L(R)) ( L(S)) ( , , ) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Blattknoten so vertauschen, dass der mit dem kleinsten Zwischenergebnis zuerst ausgewertet wird DBI 10

Beispielanfrage (1) 58 EMPLOYEE(Fname, Lname, SSN, Bdate, Address, Sex, Salary, DNO) PROJECT(Pname, Pnumber, Plocation, Dnum) WORKSON(ESSN, PNO, Hours) Finde den Nachnamen der Angestellten, die nach dem 16. 4. 58 geboren wurden und an einem Projekt namens „GOM“ arbeiten. select from where and and © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Lname Employee, Works. On, Project Pname = 'GOM' Pnumber = PNO ESSN = SSN Bdate > 58. 04. 16 DBI 10

Beispieloptimierung (1) Ergebnis der Übersetzung select from where and and 59 Lname Employee, Works. On, Project Pname = 'GOM' Pnumber = PNO ESSN = SSN Bdate > 58. 04. 16 Lname Pname=‘GOM‘ Pnumber = PNO ESSN= SSN Bdate > 58. 04. 16 EMPLOYEE © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann PROJECT WORKS_ON DBI 10

Beispieloptimierung (1) Anwendung der Regeln c 1 c 2 . . . cn (R) c 1( c 2(…( cn(R)) …)) 60 c(R S) c 1(R) c 2(S) und um Selektionen an die tiefste Stelle zu verschieben, an denen die für die Auswertung des Selektionsprädikats benötigten Attribute verfügbar sind. Lname Pnumber = PNO ESSN=SSN Pname=‘GOM‘ Bdate>58. 04. 16 PROJECT WORKS_ON EMPLOYEE © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispieloptimierung (1) 61 Anwendung der Regeln R ⋈ S S ⋈ R (R ⋈ S) ⋈ T R (S ⋈ T) und um restriktivere Join-Operationen vor weniger restriktiven anwenden zu können. Lname ESSN=SSN Pnumber = PNO Bdate>58. 04. 16 Pname=‘GOM‘ EMPLOYEE WORKS_ON PROJECT © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispieloptimierung (1) 62 Anwendung der Regel c(R S) R ⋈c S Lname ⋈ESSN=SSN ⋈Pnumber

Beispieloptimierung (1) 63 Anwendung der Regel L(R ⋈c S) L(( A 1, …, An, A 1', …, An'(R)) ⋈c ( B 1, …, Bn, B 1', …, Bn'(S))) um die Operanden frühzeitig auf die oberhalb noch benötigten Attribute einzuschränken. Lname ⋈ESSN=SSN ESSN SSN, Lname ⋈Pnumber = PNO Pnumber Pname=‘GOM‘ Bdate>58. 04. 16 ESSN, PNO EMPLOYEE WORKS_ON PROJECT © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispielanfrage (2) 64 STUDENTEN(Matr. Nr, Name, Semester) VORLESUNGEN(Vorl. Nr, Titel, SWS, gelesen. Von) PROFESSOREN(Pers. Nr, Name, Rang, Raum) HÖREN(Matr. Nr, Vorl. Nr) In welchen Semestern sind die Studenten, die Vorlesungen von Sokrates hören? select from where and and distinct s. Semester Studenten s, hören h, Vorlesungen v, Professoren p p. Name = 'Sokrates' v. gelesen. Von = p. Pers. Nr v. Vorl. Nr = h. Vorl. Nr h. Matr. Nr = s. Matr. Nr © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispieloptimierung (2) 65 Ergebnis der Übersetzung select distinct s. Semester from Studenten s, hören h, Vorlesungen v, Professoren p where p. Name = 'Sokrates' and v. gelesen. Von = p. Pers. Nr and v. Vorl. Nr = h. Vorl. Nr and h. Matr. Nr = s. Matr. Nr s. Semester p. Name = ‘Sokrates‘ v. gelesen. Von = p. Pers. Nr v. Vorl. Nr = h. Vorl. Nr h. Matr. Nr = s. Matr. Nr PROFESSOREN VORLESUNGEN STUDENTEN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann HÖREN DBI 10

Beispieloptimierung (2) 66 Anwendung der Regel c 1 c 2 . . . cn (R) c 1( c 2(…( cn(R)) …)) s. Semester p. Name = ‘Sokrates‘ v. gelesen. Von = p. Pers. Nr v. Vorl. Nr = h. Vorl. Nr h. Matr. Nr = s. Matr. Nr PROFESSOREN VORLESUNGEN STUDENTEN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann HÖREN DBI 10

Beispieloptimierung (2) Anwendung der Regeln c 1( c 2((R)) c 2( c 1((R)) und c(R S) c 1(R) c 2(S) um Selektionen an die tiefste Stelle zu verschieben, an denen die für die Auswertung des Selektionsprädikats benötigten Attribute verfügbar sind. 67 s. Semester v. gelesen. Von = p. Pers. Nr v. Vorl. Nr = h. Vorl. Nr p. Name = ‘Sokrates‘ h. Matr. Nr = s. Matr. Nr STUDENTEN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann PROFESSOREN VORLESUNGEN HÖREN DBI 10

Beispieloptimierung (2) 68 Anwendung der Regel c(R S) R ⋈c S s. Semester ⋈v. gelesen. Von = p. Pers. Nr ⋈v. Vorl. Nr = h. Vorl. Nr ⋈h. Matr. Nr = s. Matr. Nr p. Name = ‘Sokrates‘ VORLESUNGEN STUDENTEN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann PROFESSOREN HÖREN DBI 10

Beispieloptimierung (2) Anwendung der Regeln und 69 R⋈S S⋈R (R ⋈ S) ⋈ T R (S ⋈ T) um restriktivere Join-Operationen vor weniger restriktiven anwenden zu können (Optimierung der Join-Reihenfolge). s. Semester ⋈h. Matr. Nr = s. Matr. Nr ⋈v. Vorl. Nr = h. Vorl. Nr ⋈v. gelesen. Von = p. Pers. Nr STUDENTEN HÖREN STUDENTEN(Matr. Nr, Name, Semester) VORLESUNGEN(Vorl. Nr, Titel, SWS, gelesen. Von) PROFESSOREN(Pers. Nr, Name, Rang, Raum) HÖREN(Matr. Nr, Vorl. Nr) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann p. Name = ‘Sokrates‘ VORLESUNGEN PROFESSOREN DBI 10

Beispieloptimierung (2) 70 Anwendung der Regel L(R ⋈c S) L(( A 1, …, An, A 1', …, An'(R)) ⋈c ( B 1, …, Bn, B 1', …, Bn'(S))) um frühzeitig die Tupelgröße einzuschränken. s. Semester ⋈h. Matr. Nr = s. Matr. Nr h. Matr. Nr ⋈v. Vorl. Nr = h. Vorl. Nr STUDENTEN ⋈v. gelesen. Von = p. Pers. Nr HÖREN STUDENTEN(Matr. Nr, Name, Semester) VORLESUNGEN(Vorl. Nr, Titel, SWS, gelesen. Von) PROFESSOREN(Pers. Nr, Name, Rang, Raum) HÖREN(Matr. Nr, Vorl. Nr) © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann VORLESUNGEN p. Name =‘Sokrates‘ PROFESSOREN DBI 10

71 Kapitel 10. 4 Physische Anfrageoptimierung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI

Stellung in der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Code Standardisierung Code-Erzeugung Logische Optimierung algebraischer Ausdruck auf internen Dateien QEP Physische Optimierung Planparametrisierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 72 QEP Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

73 Kapitel 10. 4. 1 Einige Grundsätze © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann

Spielräume bei den Daten n 74 Basisrelationen: 1. Beispielrelationen sind jeweils gemäss dem NSM in einer Datei gespeichert, und 2. es sind zusätzlich Indexdateien eingerichtet (Primärindex: satzschlüsselbasiert, Sekundärindex: Wert nicht satzidentifizierend, Clusterindex: zusätzlich Sätze in geballter Liste), oder 3. gelegentlich ist die Datei nach einem Hashverfahren abgelegt. n Zwischenergebnisse: u u u Hier besteht im Grundsatz Wahlfreiheit bei der Dateiorganisation. Daher kann es zum Einfügen weiterer Operatoren wie z. B. Sortierung, Index-Generierung kommen. Somit kann sich der Operatorbaum gegenüber der logischen Optimierung verändern. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Spielräume bei den Operatoren n Zuordnen physischer Operatoren: u u u 75 Den Operatoren im Operatorbaum müssen Implementierungen (konkrete Algorithmen) zugeordnet werden. Die Zuordnung richtet sich bei den Operatoren auf den Blättern (den gespeicherten Relationen) nach deren statisch vorgegebener Dateiorganisation. Die freie Wahl der Dateiorganisation bei den Zwischenrelationen spiegelt sich in einer entsprechenden Wahlfreiheit bei der Zuordnung der Algorithmen wider. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Verarbeitungsvarianten (1) n Materialisierung: u Jeder Knoten wird voll abgearbeitet bevor das Ergebnis weitergereicht wird. Ø n 76 Ergebnis muss daher auf Hintergrundspeicher aufgebaut werden. Fließverarbeitung (Pipeline): u Ein Operator leitet jedes Ergebnistupel sofort an seinen übergeordneten Operator weiter. Ø u Vorteil: Reduktion des Speicherplatzes für die Zwischenergebnisse, verzahnte Ausführung. Probleme: Ø Ø Ø Tupelstrom kann an blockierenden Operatoren (Pipeline Breaker) aufgehalten werden. Das langsamste Glied in der Kette bestimmt die Geschwindigkeit: Pufferung gelegentlich erforderlich. Daher u. U. Ausweichen auf Hintergrundspeicher notwendig. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Verarbeitungsvarianten (2) 77 Fließverarbeitung schematisch . . . R © 2009 Univ, Karlsruhe, IPD,

Verarbeitungsvarianten (3) 78 Pipeline-Breaker mit Materialisierung . . . R © 2009 Univ, Karlsruhe,

Verarbeitungsvarianten (4) 79 Pipeline-Breaker: n Unäre Operationen sort u Duplikatelimination (unique, distinct) u u n Binäre Operationen u n Aggregatoperationen (min, max, sum, . . . ) Mengendifferenz Je nach Implementierung Join u Union u © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Vereinheitlichung der Operatoren (1) 80 Einheitliche Behandlung jedes Knotens: Initialisieren, Öffnen der benötigten Eingaben Realisiert beispielsweise über Klassen Angaben zu den Kosten Angaben zur Selektivität Beschaffen des nächsten Eingabetupels (nutzt scan -Operator) Aufräumen, Schließen der Iteratoren der Eingaberelation, Freigabe von Speicher, bei Blockieren: Weitergabe des kompletten Ergebnisses © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Vereinheitlichung der Operatoren (2) 81 Beispiel: Pull-basierte Fließverarbeitung next open Return Ergebnis © 2009

82 Kapitel 10. 4. 2 Operatorzuordnung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI

Selektion 83 Cluster. Index Select P: Exact Match (Schlüssel). R mit entspr. Index R P: Exact Match oder Range. R mit entspr. Index immer verwendbar Primär. Index Select R P R P: Exact Match (Nicht-Schlüssel) oder Range. R mit entspr. Index Sekundär. Index Select Linear. Select R © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Falls R Zwischenrelation: Ggf. zusätzl. Indexerzeugung R DBI 10

Projektion 84 Index. Proj Duplikatelim. , R sortiert R Duplikatelim. , R mit entspr. Index immer verwendbar Sort. Proj R L R Duplikatelim. , R unsortiert Sort Linear. Project R Falls R Zwischenrelation: Ggf. zusätzl. Indexerzeugung oder Sortierung © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann R DBI 10

Verbindung 85 Index. Join Falls R und/oder S unsortiert, zunächst sortieren Index R S S mit entspr. Index, sonst zunächst Aufbau immer verwendbar S [Sort. R. A] [Sort. S. B] R ⋈R. A=S. B R Merge. Join S immer verwendbar Hash. Join Nested. Loop R S © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Simple|Grace|Hybrid Zwischenrelationen müssen ggf. aufbereitet werden R S DBI 10

Beispiel 86 s. Semester ⋈h. Matr. Nr = s. Matr. Nr ⋈v. Vorl. Nr = h. Vorl. Nr ⋈v. gelesen. Von = p. Pers. Nr STUDENTEN HÖREN p. Name = ‘Sokrates‘ VORLESUNGEN PROFESSOREN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Beispiel 87 Sort. Project(s. Semester) Sort(s. Semester) Linear. Project(s. Semester) Index. Join(h. Matr. Nr = s. Matr. Nr) Primärindex auf Matr. Nr STUDENTEN Merge. Join (v. Vorl. Nr = h. Vorl. Nr) Sort(h. Vorl. Nr) HÖREN Sort(v. Vorl. Nr) Index. Join (v. gelesen. Von = p. Pers. Nr) Sekundärindex VORLESUNGEN auf gelesen. Von Linear. Select (p. Name = ‘Sokrates‘) PROFESSOREN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Verschmelzen von Operatoren 88 Gelegentlich lassen sich Materialisierungen auch durch Verschmelzen von Operatoren vermeiden. Beispiele: n Kombination von Selektion und Projektion. n Kombination einer Selektion mit einer Verbindung, etwa u u n Integration einer Selektion in die äußere Schleife eines Nested-Loop-Join, Integration von Selektionen in den Merge-Join. Kombination von Gruppierung und Aggregierung. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

89 Kapitel 10. 5 Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI

Stellung in der Anfragebearbeitung deklarative SQL-Anfrage Ergebnis Scanner, Parser, Sichtauflösung Ausführung DBSLaufzeitsystem Anfrage in interner Form Code Standardisierung Code-Erzeugung Logische Optimierung algebraischer Ausdruck auf internen Dateien QEP Physische Optimierung Planparametrisierung Kostenbasierte Auswahl © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann 90 QEP Auswertungsplan (Query Evaluation Plan, QEP) auf Satzzugriffsstrukturen DBI 10

Stellung in der Optimierung 91 Die Wahl des Auswertungsplans ist ein Optimierungsproblem! Lösungsraum: Mehrere deklarative SQL-Anfrage Operatorbäume, um nicht günstige Lösungen vorzeitig auszuschließen. (evtl. schrittweises) Aufspannen des Suchraums Revidierte Ausgangssituation © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Logische Optimierung Physische Optimierung Erweiterung des Lösungsraums: Diverse Zuordnungen von Algorithmen. Suche Kostenbasierte Auswahl: Aufwand richtet sich danach wie häufig die angestrebte Lösung wiederholt wird. Hoher Aufwand lohnt nur bei parametrisierten „Bester“ (guter) Plan Standardanfragen. Äquivalente Pläne DBI 10

Kostenbasierte Auswahl (1) n Optimierungskriterien: u u Für die Optimierung benötigt man eine Kostenfunktion. Sie berechnet die Gesamtkosten der Anfrage aus den Kostenabschätzung für die Einzeloperationen. Ø u Grobe Abschätzungen wurden in Kapitel 9 gegeben. In die Kostenabschätzung geht die Größe der Zwischenergebnisse zentral ein. Ø n 92 Daher spielen die Selektivitätsabschätzungen (siehe Kapitel 9) eine wichtige Rolle. Optimierung: u u Für jeden Kandidat-Auswertungsplan müssen dessen Kosten abgeschätzt werden. Gesucht sind daher Optimierungsverfahren, die den Lösungsraum sehr rasch einschränken, ohne dabei allzu viel gute Lösungen zu verlieren. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Kostenbasierte Auswahl (2) 93 deklarative SQL-Anfrage (evtl. schrittweises) Aufspannen des Suchraums Revidierte Ausgangssituation Äquivalente Pläne Suche Logische Optimierung Physische Optimierung Kostenmodell Statistiken/Histogramme Puffergröße Verwendeter Rechner Zielfunktion: Antwortzeit vs. Durchsatz „Bester“ (guter) Plan © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Kostenabschätzung (Beispiel) 94 Sort. Project(s. Semester) Schätzung Aufwand Schätzung Tupelzahl Schätzung Aufwand Sort(s. Semester) Schätzung Tupelzahl Linear. Project(s. Semester) Schätzung Aufwand Schätzung Tupelzahl Index. Join(h. Matr. Nr = s. Matr. Nr) Schätzung Aufwand Schätzung Größe(s) Tupelzahl Schätzung Merge. Join STUDENTEN (v. Vorl. Nr = h. Vorl. Nr) Aufwand Schätzung Tupelzahl Sort(v. Vorl. Nr) Schätzung Aufwand Sort(h. Vorl. Nr) Schätzung Größe(h) Tupelzahl Schätzung Index. Join HÖREN (v. gelesen. Von = p. Pers. Nr) Aufwand Schätzung Größe(v) Tupelzahl Linear. Select Schätzung VORLESUNGEN (p. Name = ‘Sokrates‘)Aufwand Größe(p) PROFESSOREN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Join-Reihenfolge 95 Beobachtung: In allen praktischen Anfragen dominieren die Verbindungsoperationen Optimierung der Reihenfolge dieser Operationen kritisch für die Anfrageoptimierung. Join-Modell (Graphische Veranschaulichung): ⋈ R Äußere (Build-) Relation: Wenn immer möglich die kleinere Relation © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann S Innere (Probe-) Relation DBI 10

Join-Bäume (2) 97 ⋈ ⋈ ⋈ R 4 ⋈ ⋈ R 1 R 2 R 1 ⋈ R 2 R 3 R 4 R 3 Teilmenge der buschigen Bäume, daher geringere Variantenzahl. Beispiel n=6: Buschige Bäume: 30. 240 Links-orientierte Bäume: 720 © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Variantenzahl (n: Zahl der Relationen): (2(n-1))! n (2(n-1))!/(n-1)! 2 2 5 1680 10 1, 76 1010 20 4, 3 1027 DBI 10

Join-Bäume (3) 98 ⋈ ⋈ ⋈ R 4 R 1 R 2 R 3 © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann Links-orientierte Bäume spiegeln das Join-Modell und damit die effizienten Algorithmen wider. Besitzen besondere Tendenz zur Entwicklung effizienter Auswertungspläne! Ausreichend zur Entwicklung des Suchraums! DBI 10

Umformung des Beispiels 99 Sort. Project(s. Semester) Sort(s. Semester) Linear. Project(s. Semester) Index. Join(h. Matr. Nr = s. Matr. Nr) Merge. Join (v. Vorl. Nr = h. Vorl. Nr) Sort(v. Vorl. Nr) Index. Join (v. gelesen. Von = p. Pers. Nr) Linear. Select (p. Name = ‘Sokrates‘) STUDENTEN Primärindex auf Matr. Nr Sort(h. Vorl. Nr) HÖREN VORLESUNGEN Sekundärindex auf gelesen. Von PROFESSOREN © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Greedy-Suche (1) 100 n Konstruiere die Lösung des vollständigen Plans schrittweise, indem mit jedem Schritt der bisher erzeugte Teilplan durch Hinzunahme einer Verbindung so erweitet wird, dass die geringsten Kosten entstehen. u Keine Garantie dass der optimale Plan gefunden wird, aber es wird ein guter Plan gefunden. u Großer Vorteil: Anzahl der zu betrachtenden Pläne ist gering. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Greedy-Suche (2) 101 Beispiel: n Verbindung der 4 Relationen KUNDE, PRODUKT, LIEFERANT, BESTELLUNG. n

Greedy-Suche (3) 102 Schritt 1: Plan Ergebnisgröße KUNDE ⋈ PRODUKT 5. 000 KUNDE ⋈ LIEFERANT 1. 000 KUNDE ⋈ BESTELLUNG 20. 000 PRODUKT ⋈ LIEFERANT 5. 000 PRODUKT ⋈ BESTELLUNG LIEFERANT ⋈ BESTELLUNG ⋈ ⋈ 20. 000 ⋈ 2. 000 BESTELLUNG Schritt 2: PRODUKT Plan Ergebnisgröße (PRODUKT ⋈ LIEFERANT) ⋈ BESTELLUNG (PRODUKT ⋈ LIEFERANT) ⋈ KUNDE LIEFERANT 20. 000 5. 000 Schritt 3: KUNDE © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Dynamische Programmierung (1) 103 Prinzip: n Zerlege Problem wiederholt in Teilprobleme. n Löse jedes Teilproblem optimiert unter Verwendung „kleinerer“ Teilprobleme. u Dieselbe Lösung kann mehrfach verwendet werden, d. h. Lösungen werden nur einmal entwickelt. Anwendung auf R 1 ⋈ R 2 ⋈. . . ⋈ Rn: n Berechne Lösungen für alle Ri und alle Kombinationen Ri ⋈ Rj (da es jeweils nur eine gibt, sind sie definitionsgemäß optimal). n Erweitere schrittweise von optimierten (k-1)-Kombinationen auf k-Kombinationen (k=3, . . n). © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Dynamische Programmierung (2) 104 n Standardverfahren in heutigen relationalen Datenbanksystemen! n Jedoch mit effizienter Aufzählung: u Anstatt zunächst alle 2 -elem, 3 -elem, . . . , n-elem Pläne sequentiell zu enumerieren: Effizientes Verschränken. u Frühzeitiges Abschneiden von Lösungswegen. Beispiel: n Verbindung der 4 Relationen KUNDE (K), PRODUKT (P), LIEFERANT (L), BESTELLUNG (B). n Einfache Kostenfunktion: Ergebnisgröße. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Dynamische Programmierung (3) 105 Basis: Menge (k=1) Ergebnisgröße Opt. Plan {K} 1. 000 K {P} 5. 000 P {L} 100 L {B} 20. 000 B Menge (k=2) Ergebnisgröße Opt. Plan {K, P} 5. 000 K ⋈ P {K, L} 1. 000 L ⋈ K {K, B} 20. 000 K ⋈ B {P, L} 5. 000 L ⋈ P {P, B} 20. 000 P ⋈ B {L, B} 2. 000 L ⋈ B © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10

Dynamische Programmierung (4) 106 Induktion (links-orientiert): Menge (k=1) Ergebnisgröße Opt. Plan {K} 1. 000 K {P} 5. 000 P {L} 100 L {B} 20. 000 B Menge (k=2) Ergebnisgröße Opt. Plan {K, P} 5. 000 K ⋈ P {K, L} 1. 000 L ⋈ K {K, B} 20. 000 K ⋈ B {P, L} 5. 000 L ⋈ P {P, B} 20. 000 P ⋈ B {L, B} 2. 000 L ⋈ B © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann In diesem Beispiel weiter verfolgt DBI 10

Dynamische Programmierung (5) 107 Induktion (links-orientiert): Menge (k=3) Ergebnisgröße Opt. Plan {K, B, L} 2. 000 (K ⋈ B) ⋈ L {K, B, P} 20. 000 (K ⋈ B) ⋈ P {P, L, K} 5. 000 (L ⋈ P) ⋈ K {P, L, B} 20. 000 (L ⋈ P) ⋈ B Summe der bisher in den Zwischenergebnissen aufgelaufenen Kosten. Opt. Plan Kosten (K ⋈ B) ⋈ L 2. 020. 000 (K ⋈ B) ⋈ P 40. 000 (L ⋈ P) ⋈ K 5. 000 (L ⋈ P) ⋈ B 25. 000 ((L ⋈ P) ⋈ B) ⋈ K Letzter Schritt: k=4 ({K, P, L, B}), also nur 1 Ergebnisgröße. © 2009 Univ, Karlsruhe, IPD, Prof. Lockemann DBI 10