5. Anfragebearbeitung und -optimierung Datenbankparameter

5. Anfragebearbeitung und -optimierung
Basisalgorithmen
5. Anfragebearbeitung und -optimierung
5. Anfragebearbeitung und -optimierung
Annahmen für die Optimierung
• Dominierender Kostenfaktor sind die Zugriffe auf Seiten der Datenbank (Peripheriespeicher).
• Der Peripheriespeicher wird auch für die Speicherung von Zwischenergebnissen
genutzt.
• Indexe sind B*-Bäume.
• Für jede Operation wird ein Zwischenergebnis in Form einer Relation erzeugt, die
auf dem Peripheriespeicher abgelegt wird.
• Jedes Tupel wird eindeutig durch einen Tupelidentifikator (TID) identifiziert.
• Operationen auf Tupelmengen (-listen) werden im Hauptspeicher ausgeführt.
• Es stehen Operation zur Verfügung für:
– Tupelvergleich
– Zugriff auf ein Tupel einer im Hauptspeicher liegenden Seite über die TID
• Diese Operationen finden im Hauptspeicher statt.
• Identifikatoren für Relationen (RelID) und Indexe (IndID)
Themen:
•
•
•
•
Vorstellung wichtiger interner Basisalgorithmen für grundlegende Operationen
Überblick: Grundprinzipien der Optimierung
Phasen der Optimierung und dabei eingesetzte Methoden
Fallbeispiele
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
212
Basisalgorithmen
Datenbankparameter
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
214
Basisalgorithmen
Indexarten:
•
•
•
•
•
nr : Anzahl der Tupel in Relation r
br : Anzahl der Seiten, die Tupel aus r beinhalten
sr : durchschnittliche Größe von Tupeln aus r
bs: Blockgröße
fr : Blockungsfaktor
— bs
fr =
sr
Es gilt:
‰ ı
nr
br ≥
fr
• V (A, r): Anzahl der verschiedenen Werte für Attribut A in Relation r
• SC(A, r): Selektionskardinalität, d.h. die durchschnittliche Anzahl an Ergebnistupeln bei σA=x(r)
nr
SC(A, r) =
V (A, r)
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
Basisalgorithmen
213
• Primärindex: Liefert zu einem Attributwert höchstens eine TID
• Sekundärindex: Liefert mehrere Tupel (TIDs) zu einem Atrributwert
Operationen:
• fetch-tupel( RelID, TID ) −→ Tupel
Holt ein Tupel in den Tupel-Puffer.
• fetch-TID( IndID, AttrWert ) −→ TID
Bestimmt den TID zu einem Attributwert (bei einem Primärindex).
• Scans auf Relationen und Indexen
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
215
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
select *
from Kunde
where KName = ’Meyer’
Auswertung:
tid := fetch-TID( Kunde-KName-IndID, ’Meyer’ );
tupel := fetch-tupel( Kunde-RelID, tid );
output( tupel );
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
• Anzahl an Lese- und Schreiboperationen ist im wesentlichen linear.
• Lese- und Schreiboperationen auf
den Partitionen sind sequentiell.
• Große Puffer beim Lesen und
Schreiben verwenden!
• Vergleichsoperationen beim Mischen O(n log n), aber diese
Vergleiche finden im Hauptspeicher statt!
Beispiel: SQL-Anfrage:
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
216
Basisalgorithmen
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
218
Basisalgorithmen
Sortierung
Scan
☞ Bekannte Sortierverfahren wie Quick-Sort sind nur bedingt für Datenbanken geeignet.
Ein Scan greift auf alle Tupel einer Relation zu, eventuell in Verbindung mit einer Selektion oder Projektion. Dies entspricht dem Arbeiten mit einem Cursor auf einer Relation.
☞ Von Interesse sind Sortierverfahren, die den Peripheriespeicher mit einbeziehen.
Sort-Merge-Verfahren:
• Die Relation wird durch eine Operation partition in gleich große Teile zerlegt, die
im Hauptspeicher sortiert werden können.
• Die Teile werden mit einer Operation merge sortiert zusammengeführt.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
217
• Full Table Scan
Sequentielles Lesen aller Seiten, die zu einer Relation gehören. Aufwand: br
• Index Scan
Nutzt einen Index zum Auslesen der Tupel in Sortierreihenfolge. Aufwand: Seiten
mit Tupel der Ergebnismenge plus Anzahl der benötigten Index-Seiten.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
219
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
Scan-Operationen:
Index-Scan:
• open-rel-scan( RelID ) −→ ScanID
Initialisiert einen Relationen-Scan.
• open-index-scan( IndID, Min, Max ) −→ ScanID
Initialisiert einen Index-Scan auf einem Primär- oder Sekundärindex. Min und Max
bestimmen den Bereich des Scans.
• next-TID( ScanID ) −→ TID
Liefert die nächste TID des Scans.
• end-of-scan( ScanID ) −→ Boolean
Prüft, ob noch weitere TIDs verfügbar sind.
• close-scan( ScanID )
Schließt einen Scan.
scanid := open-index-scan( Dozenten-Name-ID, ’Becker’, ’Borutzky’ );
tid := next-TID( scanid );
while not end-of-scan( scanid ) do
tupel := fetch-tupel( DozentenID, tid );
output( tupel );
tid := next-TID( scanid );
end
close( scanid );
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
220
Basisalgorithmen
Beispiel:
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
222
Basisalgorithmen
Selektion
select *
from Dozenten
where Name between ’Becker’ and ’Borutzky’
• Arten der Selektion: exakte Suche, Bereichssuche, mit Junktoren zusammengesetzte Selektionsbedingung
• Wir betrachten den letzen Fall: Selektionsbedingung φ mit Junktoren (and, or, not)
zusammengesetzt aus atomaren Bedingungen.
• Verschiedene Arten der Auswertung:
– Direkte Auswertung
– konjunktiven Normalform
– Filtermethode
Relationen-Scan:
scanid := open-rel-scan( DozentenID );
tid := next-TID( scanid );
while not end-of-scan( scanid ) do
tupel := fetch-tupel( DozentenID, tid );
if tid.Name >= ’Becker’ and tid.name <= ’Borutzky’ then
output( tupel );
endif
tid := next-TID( scanid );
end
close( scanid );
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
221
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
223
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
5. Anfragebearbeitung und -optimierung
Direkte Auswertung:
Projektion
• Anfrage der Form σφ(r) mit Selektionsbedingung φ für Relation r.
• Mit Relationen-Scan wird φ(t) für alle t ∈ r ausgewertet.
• Aufwand O(nr ) bzw. br .
•
•
•
•
konjunktiven Normalform (KNF):
• Zugriffspfade einsetzen, hierzu φ geeignet umformen
• φ wird in die konjunktive Normalform überführt, d.h. φ wird äquivalent durch eine
Konjunktion von Disjunktionen von Literalen ausgedrückt.
• Auswahl einer Disjunktion, die gut durch einen Index ausgewertet werden kann
(z.B. A = c mit Index für Attribut A).
• Index-Scan auf A und Auswertung der anderen Bedingungen auf Basis der TIDs
des Index-Scans.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
224
Basisalgorithmen
Filtermethode:
Hier: Projektion mit Eliminierung von Duplikaten
Index-Scan hilft bei der Duplikateliminierung
Projektion auf indexierte Attribute ist ohne Tupelzugriff möglich.
Projektion πA (r):
1. r nach A sortieren
2. t ∈ r in Ergebis nehmen, falls t.A 6= previous(t).A
• Zeitaufwand: O(nr log nr ), falls schon sortiert O(nr ), falls Schlüssel K ⊆ A :
O(nr )
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
226
Basisalgorithmen
Aggregierung und Gruppierung
• Alle Bedingungen, die nicht durch einen Index ausgewertet werden k önnen, werden auf true gesetzt; vereinfachte Bedingung φ0
• Dann wird r := σφ0 (r) mit Indexunterstützung ausgewertet (z.B. mittels KNF).
• Anschließend wird σφ(r0 ) auf dem kleineren Zwischenergebnis r 0 ermittelt.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
Basisalgorithmen
225
• Gruppierung und Aggregierung mit Duplikatelimination können durch einen modifizierten Projektionsoperator realisiert werden.
• Sie haben daher ebenfalls eine Komplexität von O(nr log nr )
• Für gewisse Aggregatsfunktion sind spezielle Realisierungen m öglich, z.B.
count(*) auf einer vollständigen Relation.
• Hierfür könnte direkt die Metainformation zu einem Primärindex genutzt werden.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
227
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
Verbunde
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
Sort-Merge-Join:
• Der Verbund (Join) ist in gewisser Weise die wichtigste Operation der relationalen
Algebra,
• da dies die einzige Operation ist, die verschiedene Relationen zusammenführt.
• Varianten der Berechnung:
– Schleifeniteration (nested loop)
– Mischtechniken (merge join)
– Hash-Techniken (hash join)
• Gegeben seien Relationen r, s, zu denen der Verbund r ./φ s auf Basis einer
Verbundbedingung φ berechnet werden soll.
• Semantik: r ./φ s ⇔ σφ(r × s)
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
228
Basisalgorithmen
• Seien R, S die Attributmenge von r, s. Betrachte Equi-Verbund über eine Attributmenge A ⊆ R ∩ S .
• Zuerst Sortierung von r und s nach A.
• Dann Mischphase:
1. Falls tr .A < ts .A: lese nächstes tr ∈ r
2. Falls tr .A > ts .A: lese nächstes ts ∈ s
3. Falls tr .A = ts.A:
(a) Bilde alle tr × t0s mit t0s.A = ts.A.
(b) Lese nächstes tr .
• Aufwand: Abhängig von der Selektivität von A, günstig falls A Schlüssel.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
230
Basisalgorithmen
Nested Loop Join:
Hash-Join:
for each tr ∈ r do
for each ts ∈ s do
if φ(tr , ts ) = true then
output(tr × ts )
endif
end
end
• Tupel aus r und s werden in Dateien Fr und Fs mit jeweils k Blöcken auf der Basis
von A gehasht.
• Möglicher Wert für k: k = max{V (A, r), V (A, s)}
• Tupel aus dem Block i von Fr werden mittels von Nested-Loop-Join mit den Tupeln
aus Block i von Fs verbunden.
• tr × ts bezeichnet die Verschmelzung der Tupel tr und ts .
• Verbesserte Variante für r ./A=B s: Falls Index auf B von s, dann Berechnung
der inneren Schleife durch σB=tr .A(s).
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
229
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
231
5. Anfragebearbeitung und -optimierung
Basisalgorithmen
Grundprinzipien und Beispiele
Basissprachen:
Mengenoperationen
•
•
•
•
5. Anfragebearbeitung und -optimierung
• SQL
• Relationenalgebra
• Operatorbäume, Zugriffspläne
Operationen: Vereinigung, Schnitt, Differenz
Weiterhin wichtig: Vereinigung mit Duplikateliminierung
problemlos: Vereinigung
Ziel der Optimierung:
Ansätze zur Berechnung der anderen Operationen analog der Berechnung von
Verbunden:
– Schleifeniteration
– Mischtechniken
– Hash-Techniken
• schnelle Anfragebearbeitung
⇒ wenig Seitenzugriffe
⇒ wenig Tupel- bzw. Indexzugriffe
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
232
Grundprinzipien und Beispiele
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
234
Grundprinzipien und Beispiele
Teilziele der Optimierung:
Grundprinzipien der Anfrageoptimierung
1. Selektionen so früh wie möglich, um Zwischenergebnisse klein zu halten
2. Basisoperationen zusammenfassen und ohne Zwischenspeicherung realisieren
• In einem RDBS wird mit SQL nur die Ergebnismenge festgelegt (deskriptiver Ansatz).
• Eine möglichst gute Strategie zur Anfragebearbeitung zu bestimmen ist Aufgabe
des Optimierers.
• Eingabe für den Optimierer: SQL-Anfragen
• Ausgabe des Optimierers: Umsetzung in eine iterative Formulierung basierend auf
den behandelten Basisalgorithmen;
Zugriffsplan bzw. dessen Umsetzung in Code
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
233
3. Redundante Operationen, Idempotenzen oder leere Zwischenrelationen entfernen
4. Wiederverwendung von Zwischenergebnissen durch Zusammenfassung gleicher
Teilausdrücke
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
235
5. Anfragebearbeitung und -optimierung
Grundprinzipien und Beispiele
5. Anfragebearbeitung und -optimierung
Beispiele
Relationen:
Direkte Auswertung
KUNDE { KName, Kadr, Kto }
AUFTRAG { KName, Ware, Menge }
• R1 := Kunde × AUFTRAG
– lesen: (100/5 ∗ 10.000/10) = 20.000 Seiten
– schreiben: (100 ∗ 10.000)/3 ≈ 333.000 Seiten
• R2 := σSEL(R1 )
– lesen: (100 ∗ 10.000)/3 ≈ 333.000 Seiten
– schreiben: 50/3 ≈ 17 Seiten
• ERG := πPROJ (R2 )
– lesen: 17
– schreiben: 1
Anfrage:
SELECT KUNDE.KName, KUNDE.Kto
FROM KUNDE, AUFTRAG
WHERE KUNDE.KName = AUFTRAG.KName and AUFTRAG.Ware = ’Kaffee’
Bezeichnungen:
PROJ
SEL
:=
:=
KUNDE.KName, KUNDE.Kto
KUNDE.KName = AUFTRAG.KName and AUFTRAG.Ware = ’Kaffee’
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
ca. 687.000 Seitenzugriffe, ca. 333.000 Seiten Zwischenspeicherung
236
Grundprinzipien und Beispiele
Annahmen:
•
•
•
•
•
•
•
Grundprinzipien und Beispiele
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
238
Grundprinzipien und Beispiele
Optimierte Auswertung
Relation KUNDE: 100 Tupel, eine Seite faßt 5 Tupel
Relation AUFTRAG: 10.000 Tupel, eine Seite fasst 10 Tupel
• R1 := σWare=’Kaffee’(AUFTRAG)
– lesen: 10.000/10 = 1000
– schreiben: 50/10 = 5
• R2 := KUNDE ./KName=KName R1
– lesen: 100/5 ∗ 5 = 100
– schreiben: 50/3 = 17
• ERG := πPROJ (R2 )
– lesen: 17
– schreiben: 1
Für 50 Aufträge gilt: Ware = ’KAFFEE’
Es passen 50 Tupel der Form (KName, Kto) auf eine Seite.
3 Tupel von KUNDE × AUFTRAG passen auf eine Seite.
Puffer für Relationen hat Größe 1.
Tupel werden stets komplett gespeichert.
ca. 1140 Seitenzugriffe insgesamt
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
237
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
239
5. Anfragebearbeitung und -optimierung
Grundprinzipien und Beispiele
5. Anfragebearbeitung und -optimierung
Mit Indexausnutzung
Von SQL zur Relationenalgebra
• R1 := σWare=’Kaffee’(AUFTRAG) über I(AUFTRAG(Ware))
– lesen: ≥ 5, ≤ 50 Seiten
– schreiben: 50/10 = 5
• R2 := sortiere R1 nach KName
– lesen + schreiben: 5 ∗ log 5 ≈ 15 Seiten
• R3 := KUNDE ./KName=KName R2
Merge-Join über den Index I(KUNDE(KName)) und der sortierten Relation R 2 .
– lesen: 100/5 + 5 = 25
– schreiben: 50/3 = 17
• ERG := πPROJ (R3 )
– lesen: 17
– schreiben: 1
Operatorbaum:
π A1,...,Am
5. Anfragebearbeitung und -optimierung
σF
select A1, . . . , Am
from R1, . . . , Rn
where F
Anfrage in Form der Relationenalgebra:
Rn
πA1,...,Am (σF (R1 × · · · × Rn))
R3
Insgesamt maximal ca. 130 Seitenzugriffe, minimal ca. 85, durch sogenanntes Pipelining ist eine weitere Minimierung möglich.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
Grundprinzipien und Beispiele
240
Grundprinzipien und Beispiele
Phasen der Anfragebearbeitung
R1
R2
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
242
Grundprinzipien und Beispiele
Diese Umsetzung geht von einer stark vereinfachten Situation aus.
Für reale SQL-Anfragen muß zusätzlich folgendes geleistet werden:
1. Übersetzung und Sichtexpansion
Übersetzung in einen Operatorbaum (relationale Algebra), Einsetzen der Sichtdefinitionen, Auflösung von Unteranfragen
2. Logische/algebraische Optimierung
Umformung des Anfrageplans unabhängig von der physischen Ebene, z.B. Selektionen so früh wie möglich durchführen
3. Interne Optimierung
Berücksichtigung konkreter Speicherungsformen, Auswahl von Algorithmen. Erstellung mehrerer alternativer interner Pläne
4. Kostenbasierte Auswahl
Statistikinformation für die Auswahl eines internen Plans nutzen
5. Code-Erzeugung
Umwandlung des ausgewählten Zugriffsplans in ausführbaren Code bzw. direkte
Abarbeitung des Zugriffsplans
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
241
• Erkennen von Verbunden (./) statt Kreuzprodukten (×)
• Auflösung von Unteranfragen, z.B. Differenz statt not exists
• SQL-Konstrukte, die in der Relationenalgebra nicht ausgedrückt werden k önnen,
z.B. group by, order by, ...
• Sichtexpansion ist ein rekursiver Prozeß, da Sichten basierend auf Sichten definiert werden können.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
243
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
Entfernung redundanter Operationen:
Algebraische Optimierung
LANGEWEG sei eine Sicht, die wie folgt definiert ist:
• Unter der logischen Optimimierung versteht man die Phase der Optimierung ohne
Zugriff auf das interne Schema und Statistikinformationen.
• Notation: Relationenalgebra bzw. Erweiterungen hiervon
• Die algebraische Optimierung basiert auf der Ersetzung von Termen der Relationenalgebra mit Hilfe von Äquivalenzen.
• Diese Äquivalenzen stellen gerichtete Ersetzungsregeln dar.
• heuristische Methode: Anwendung dieser Ersetzungsregeln, um einen besseren
Plan zu erzeugen.
LANGEWEG := BÜCHER ./ πISBN,Datum(σDatum≤31.12.1999 (AUSLEIHE))
Jemand stellt die folgende Anfrage:
πTitel(BÜCHER ./ LANGEWEG)
Sichtexpansion liefert:
πTitel(BÜCHER ./ BÜCHER ./ πISBN,Datum(σDatum≤31.12.1999 (AUSLEIHE)))
Regel: Idempotenz: R ./ R = R
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
244
Algebraische Optimierung
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
246
Algebraische Optimierung
Verschieben von Selektionen:
Prinzipien der algebraischen Optimierung
Wir betrachten die Anfrage
σAutor=’Witt’(BÜCHER ./ πISBN,Datum(. . .))
Beispiel-Datenbank:
Hier wird erst der volle Verbund berechnet, auf dem später die Selektion Autor=’Witt’
angewendet wird.
BÜCHER = { Titel, Autor, Verlagsname, ISBN }
VERLAGE = { Verlagsname, VerlagsAdr }
ENTLEIHER = { EntlName, EntlAdr, EntlKarte }
AUSLEIHE = { EntlKarte, ISBN, Datum }
Durch eine fühere Selektion wird das Ergebnis des Joins deutlich kleiner:
(σAutor=’Witt’(BÜCHER)) ./ πISBN,Datum(. . .)
Regel: Selektion und Verbund kommutieren, wenn die Selektionspr ädikate dies zulassen.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
245
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
247
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
• SelProj: Die Operatoren π und σ kommutieren, wenn das Prädikat F auf den
Projektionsattributen definiert ist:
Reihenfolge von Verbunden:
Die Reihenfolge von Mehrfachverbunden beeinflußt ebenfalls die Gr öße der Zwischenergebnisse.
σF (πX (R)) ←→ πX (σF (R)) falls attr(F ) ⊆ X
Ohne Kenntnis der Statistikinformationen kann hier aber keine eindeutige Regel angegeben werden.
Ist dies nicht der Fall, muß die Projektion um die notwendigen Attribute erweitert
werden:
πX1 (σF (πX1 ∪X2 (R))) ←→ πX1 (σF (R)) falls attr(F ) ⊆ X1 ∪ X2
Wir betrachten den Dreifachverbund
(VERLAGE ./ AUSLEIHE) ./ BÜCHER
Der erste Verbund entartet zum kartesischen Produkt. Besser ist daher die folgende
Auswertung:
VERLAGE ./ (AUSLEIHE ./ BÜCHER)
Regel: ./ ist assoziativ und kommutativ
In der Praxis wird der Pfeil nach rechts benutzt.
• SelJoin: Die Operatoren σ und ./ kommutieren, falls die Selektionsattribute alle
aus einer der beiden Relationen stammen:
σF (R ./ S) ←→ σF (R) ./ S falls attr(F ) ⊆ R
Falls das Selektionsprädikat derart aufgesplittet werden kann, daß in F = F1 ∧ F2
die beiden Teile der Konjunktion passende Attribute haben, so gilt:
σF (R ./ S) ←→ σF1 (R) ./ σF2 (S) falls attr(F1 ) ⊆ R und attr(F2 ) ⊆ S
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
248
Algebraische Optimierung
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
Auf jeden Fall kann ein F1 mit Attributen der Relation R abgespalten werden, wenn
F2 Attribute von R und S betrifft:
Algebraische Regeln
σF (R ./ S) ←→ σF2 (σF1 (R) ./ S) falls attr(F1 ) ⊆ R
• KommJoin: Der Operator ./ ist kommutativ:
• SelUnion: σ und ∪ kommutieren:
R1 ./ R2 ←→ R2 ./ R1
σF (R ∪ S) ←→ σF (R) ∪ σF (S)
• AssozJoin: Der Operator ./ ist assoziativ:
• SelDiff: σ und \ kommutieren:
(R1 ./ R2) ./ R3 ←→ R1 ./ (R2 ./ R3)
• ProjProj: Beim Operator π dominiert in der Kombination der äußere Parameter
den inneren:
πX (πY (R)) ←→ πX (R)
• SelSel: Eine Kombination von Prädikaten bei σ entspricht dem logischen Und.
Daher können die Formeln in der Reihenfolge vertauscht werden.
σF1 (σF2 (R)) ←→ σF1∧F2 (R) ←→ σF2 (σF1 (R))
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
250
σF (R \ S) ←→ σF (R) \ σF (S)
oder
σF (R \ S) ←→ σF (R) \ S
• ProjJoin: π und ./ kommutieren:
πX (R ./ S) ←→ πX (πY1 (R) ./ πY2 (S))
249
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
251
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
mit
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
Einfacher Optimierungsalgorithmus
Y1 = (X ∩ attr(R)) ∪ (attr(R) ∩ attr(S))
und
Y2 = (X ∩ attr(S)) ∪ (attr(R) ∩ attr(S))
Bemerkung: Die für den natürlichen Verbund benötigten Attribute müssen beim
Hereinziehen der Projektion erhalten bleiben.
• ProjUnion: π und ∪ kommutieren:
πX (R ∪ S) ←→ πX (R) ∪ πX (S)
• Es gibt eine Reihe weiterer Regeln: Distributivgesetz für ./ und ∪, Distributivgesetz
für ./ und \, etc.
1. Komplexe Selektionsprädikate werden aufgelöst (Regel SelSel und gegebenenfalls Regeln der Auflösung für ¬ und ∨
2. Mittels SelJoin, selProj, SelUnion und SelDiff werden Selektionen m öglichst weit
in Richtung der Blätter verschoben. Gegebenenfalls müssen Selektionen gem äß
SelSel vertauscht werden.
3. ProjProj, ProjJoin, ProjUnion ermöglichen es, die Projektionen ebenfalls in Richtung der Blätter zu verschieben.
Diese Einzelschritte werden in der genannten Reihenfolge solange ausgeführt, bis
keine Ersetzungen mehr möglich sind.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
252
Algebraische Optimierung
• Idempotenzen
IdemUnion: R ∪ R ←→ R
IdemSchnitt: R ∩ R ←→ R
IdemJoin: R ./ R ←→ R
IdemDiff: R \ R ←→ {}
• Leere Relationen
LeerUnion: R ∪ {} ←→ R
LeerSchnitt: R ∩ {} ←→ R
LeerJoin: R ./ {} ←→ {}
LeerDiffRechts: R \ {} ←→ R
LeerDiffLinks: {} \ R ←→ {}
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
254
Algebraische Optimierung
Beispiele: algebraische Optimierung
Gegeben sei die folgende Sicht AUSLEIH INFO:
πTitel,Autor,Verlagsname,...(AUSLEIHE ./ ENTLEIHER ./ BÜCHER)
Wir untersuchen die folgende Anfrage:
select Titel
from AUSLEIH_INFO
where DATUM < 1.1.03 and Autor = ’Witt’;
Bemerkung: Man beachte die doppelte Projektion!
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
253
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
255
5. Anfragebearbeitung und -optimierung
Algebraische Optimierung
π Titel
σ Datum<1.1.03 and Autor=’Witt’
Anwendung der algebraischen Regeln:
1. SelSel: Aufteilung der Selektion
2. SelProj: Verschiebung der Selektionen nach innen
• zweimalige Verschiebung von
σAutor=’Witt’
• zweimalige Verschiebung von
σDatum<1.1.03
π ProjList
BÜCHER
AUSLEIHE
3. ProjProj: Zusammenfassung
Projektionen
der
ENTLEIHER
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
5. Anfragebearbeitung und -optimierung
256
Algebraische Optimierung
Ergebnis:
πTitel(σDatum<1.1.03(AUSLEIHE)) ./ ENTLEIHER ./ σAutor=’Witt’(BÜCHER)
Anfrageplan nach Verschieben der Selektionen: Tafel ✎
• Um Zwischenergebnisse klein zu halten, können zusätzlich Projektionen mittels
ProjJoin eingeführt werden.
• Wenn Zwischenergebnisse gespeichert werden müssen, sollte dies auf jeden Fall
passieren.
Anfrageplan mit zusätzlichen Projektionen: Tafel ✎
Weiteres Beispiel: Tafel ✎
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, WS 05/06
257