Folienkopien zum Kapitel 2 ()

Anfrageoptimierung
Java : Assembler
(Faktor 2-10) , aber
SQL : SQL optimiert (Faktor 1-100 und mehr) - Warum?
- Datenbankpuffer
- Speicherstrukturen
- Logische Anfrageoptimierung
- Physische Anfrageoptimierung
- Anfragepläne und Planbewertung
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 1
Datenbankpuffer
Gefährdete Daten
Gesicherte Daten
laden
Datenbank im
Hauptspeicher
Datenbank
auf
Communication-Area
speichern
Platte
Anwendungsprogramm
im Hauptspeicher
DBMS-eigene Hauptspeicherverwaltung wegen:
- kontrolliertes auslagern (notwendig für Recovery-Fähigkeit)
- optimierte Seitenverdrängung für Anfrageoptimierung
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 2
1
Speicherstrukturen und Indizes
Primär-Index
Datenbank
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
Sekundär-Index
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 3
Speicherstrukturen - B-Bäume
einheitliche Baumtiefe
mindestes p/2 Pointer pro Knoten
(außer Wurzel und Blätter)
p = maximale Pointer-Zahl
q = reale Pointerzahl , q≤
≤p
q-1 = Key-Anzahl
Tree
Ptr 1
Key Data
1
Ptr 1
Tree
Ptr 2
Data
B-Tree 1
Key
q-1
Data
Ptr q-1
Tree
Ptr q
Data
B-Tree 2
B-Tree q
Schlüssel in Sub-B-Tree K < Key K < Schlüssel in Sub-B-Tree K+1
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 4
2
Speicherstrukturen - B-Bäume
left sibling
Tree
Ptr 1
parent
Key Data
1
Ptr 1
Tree
Ptr 2
q
Key
q-1
Data
B-Tree
right sibling
Data
Ptr q-1
Tree
Ptr q
Data
B-Tree
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
B-Tree
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 5
Speicherstrukturen - B+-Bäume
einheitliche Baumtiefe
mindestes p/2 Pointer pro Knoten
(außer Wurzel und Blätter)
p = maximale Pointer-Zahl
q = reale Pointerzahl , q≤
≤p
q-1 = Key-Anzahl
Tree
Ptr 1
Key Data
1
Ptr 1
Tree
Ptr 2
Data
B+-Tree 1
Key
q-1
Data
Ptr q-1
Tree
Ptr q
Data
B+-Tree 2
B+-Tree q
Schlüssel in Sub-B+-Tree K < Key K < Schlüssel in Sub-B+-Tree K+1
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 6
3
Blätter von B+-Bäumen
• enthalten Zeiger auf die Daten
• enthalten keine Zeiger mehr auf Sub-Trees
• enthalten Verweis auf Nachfolge-Blatt-Knoten
Tree
Ptr 1
Key Data
1
Ptr 1
Tree
Ptr 2
Key
q-1
Data
Data LeafPtr q-1 Ptr q
Data
Schlüssel entsprechen Schlüsseln in den Daten.
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 7
Beispiel zu B+-Bäumen
• 64 bit–Adressierung von Daten (=8 Byte für jeden Zeiger)
• 4K = 4096 Byte pro Block (bzw. Seite)
• 4 Byte für Integer-Schlüssel
==> Blatt-Seiten haben Platz für
einen Leaf-Ptr (8 Byte) und
4096 div ( 4+8 ) = 340 (Key,DataPtr) - Paare
==>
Verzweigungsgrad 340
innere Seiten haben Platz für
einen letzten Teilbaum-Ptr (8 Byte) und
4096 div ( 4+8 ) = 340 (Key,DataPtr) - Paare
==>
Verzweigungsgrad 341
B+-Baum-Tiefe
1
2
3
4
minimal bzw.
0
2*170
2*171*170
2*171*171*170
Datenbanken und Informationssysteme 1
-
maximal adressierbare Datensätze
340
341*340 ≈ 105
341*341*340 ≈ 4* 107
341*341*341*340 ≈ 1010
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 8
4
Speicherstrukturen - Hashing
Hash-Funktion
insert:
Suche:
h : Key
voll ?
auch im
Bucket (=Datenbehälter)
Überlaufbehälter
Überlaufbehälter
h(Key)
Überlaufbehälter
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 9
Selektivität von Anfragen
Entscheidend für die Größe eines Zwischenergebnisses
Selektivität der Selektion mit Bedingung B :
Selektivität ( SB(R) ) = | SB(R) | / | R |
Selektivität des Joins mit Bedingung B :
Selektivität ( R |X|B S ) = | R |X|B S | / ( | R | * | S | )
geschätzt (z.B. durch Stichproben, Histogramme)
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 10
5
Ziel der logischen Anfrageoptimierung
SQL-Anfragen
select A1,...,An from R1,..., Rm where B
entsprechen Algebra-Ausdruck
P(A1,...,An) SB ( R1 x ... x Rm )
- sehr große Zwischenergebnisse
==> Aufgabe:
dasselbe Ergebnis mit
kleineren Zwischenergebnissen erzielen.
z.B. Selektion und Projektion soweit wie möglich nach innen.
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 11
Logische Anfrageoptimierung – Beispiel
select S.Vorname, S.Name
from Student S, Hört H, Kurs K
where K.titel = 'Datenbanken' and K.kursnr = H.kursnr and S.mnr=H.mnr ;
200
P S.Vorname, S.Name
|
200
S K.titel = 'Datenbanken' and K.kursnr = H.kursnr and S.mnr=H.mnr
|
500.000.000.000
X
500.000.000
/ \
1.000
X
K
/ \
S
H
Annahme:
10.000
50.000
10000 Studenten, die im Schnitt 5 Kurse hören
1000 Kurse, 2 davon Datenbanken mit je 100 Hörern
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 12
6
Logische Anfrageoptimierung – Beispiel
Eine mögliche Optimierung :
200
P S.Vorname, S.Name
|
|X|
200
K.kursnr = H.kursnr
50.000
2
S K.titel = 'Datenbanken'
S.mnr=H.mnr \
/
\
\
1.000
S
H
K
/
|X|
10.000
\
50.000
Annahme:
10000 Studenten, die im Schnitt 5 Kurse hören
1000 Kurse, 2 davon Datenbanken mit je 100 Hörern
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 13
Logische Anfrageoptimierung – Beispiel
Eine bessere Optimierung :
200
P S.Vorname, S.Name
|
200
|X|
S.mnr=H.mnr
/
S
\
|X|
200
K.kursnr = H.kursnr
10.000
/
H
50.000
1.000
\
2
S K.titel = 'Datenbanken'
|
K
Annahme:
10000 Studenten, die im Schnitt 5 Kurse hören
1000 Kurse, 2 davon Datenbanken mit je 100 Hörern
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 14
7
Regeln der logischen Anfrageoptimierung
Vereinigung, Durchschnitt, kartesisches Produkt und Join
sind kommutativ und assoziativ .
R1 U R2 = R2 U R1
R1 Schnitt R2 = R2 Schnitt R1
R1 X R2 = R2 X R1
R1 |X|B R2 = R2 |X|B R1
( R1 U R2 ) U R3 = R1 U ( R2 U R3 )
( R1 Schnitt R2 ) Schnitt R3 = R1 Schnitt ( R2 Schnitt R3 )
( R1 X R2 ) X R3 = R1 X ( R2 X R3 )
( R1 |X|B R2 ) |X|B R3 = R1 |X|B ( R2 |X|B R3 )
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 15
Regeln der logischen Anfrageoptimierung
Selektionen kann man splitten und untereinander vertauschen :
SB1 and B2(R) = SB1(SB2(R)) = SB2(SB1(R))
Selektionen kann man in Vereinigung, Differenz und Schnitt
schieben :
SB ( R1 U R2 ) = SB ( R1 ) U SB ( R2 )
SB ( R1 - R2 ) = SB ( R1 ) - SB ( R2 )
SB ( R1 Schnitt R2 ) = SB ( R1 ) Schnitt SB ( R2 )
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 16
8
Regeln der logischen Anfrageoptimierung
Selektion in ein Join-Argument schieben werden,
SB ( R1 |X|B2 R2 ) = SB ( R1 ) |X|B2 R2
falls B nur Attribute aus R1 verwendet
Selektion in ein Argument eines kartesischen Produktes schieben
SB ( R1 X R2 ) = SB ( R1 ) X R2
falls B nur Attribute aus R1 verwendet
Wenn das weder für R1 noch für R2 geht,
d.h., falls B Attribute aus R1 und R2 verwendet:
Selektion angewandt auf kartesisches Produkt zu Join machen
SB ( R1 X R2 ) = R1 |X|B R2
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 17
Regeln der logischen Anfrageoptimierung
Projektion und Selektion sind vertauschbar, sofern die Projektion alle für die
Selektionsbedingung benötigten Attribute erhält:
SB ( PA1,...,Am ( R1 ) ) = PA1,...,Am ( SB ( R1 ) )
falls B nur Attribute aus A1,...,Am verwendet.
Projektion in Vereinigung schieben
PA1,...,Am ( R1 U R2 ) = PA1,...,Am ( R1 ) U PA1,...,Am ( R2 )
Projektion in ein Join-Argument schieben, sofern die Join-Attribute nicht
wegprjoziert werden
PA1,...,Am ( R1 |X|B R2 ) = PA1,...,Am ( ( PA1,...,Am,AB1,...,ABn ( R1 ) ) |X|B R2 )
wobei AB1,...,ABn die in der Join-Bedingung B benötigten Attribute aus R1 sind.
Projektionen können zusammengefasst und zusätzlich eingefügt werden
PA1,...,Am ( R1 ) = PA1,...,Am ( PA1,...,Am,AB1,...,ABn ( R1 ) )
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 18
9
Logische Anfrageoptimierung - Schritte
SQL-Anfrage als logischen Anfragebaum darstellen
auf diesem Baum folgende Optimierungen durchführen:
•
Selektionen aufsplitten und nach unten (innen) schieben.
•
Selektionen und kartesische Produkte zu Joins
zusammenfassen.
•
Joinreihenfolge mit kleinsten Zwischenergebnissen
bestimmen.
•
Projektionen ggf. aufsplitten und möglichst weit
nach unten im Anfragebaum schieben.
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 19
Physische Anfrageoptimierung
Ziel : Plattenzugriffe minimieren
Denn : 1 Plattenzugriff kostet etwa soviel wie 100.000 bis 1000.000
Hauptspeicheroperationen
Anfragebaum in gleichwertigen Iteratorbaum übersetzen:
Iterator P S.Vorname, S.Name
|
Iterator |X|
1 Iterator pro Algebra-Operator und
1 Iterator pro Relation
S.mnr=H.mnr
/
Iterator-Rel(S)
\
Iterator |X|
K.kursnr = H.kursnr
/
Iterator-Rel(H)
\
Iterator S K.titel = 'Datenbanken'
|
Iterator-Rel(K)
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 20
10
Iterator-Konzept
Abstrakter Datentyp Iterator:
Open( )
initialisiert des Iterators
Next( )
gibt nächstes Tupel zurück/weiter
Close( )
schließt Iterator, gibt Ressourcen frei
Size( )
schätzt Größe des Ergebnisses
auf der Basis der Schätzung für die Eingangsvariablen
Cost( )
schätzt Kosten des Ergebnisses
auf der Basis der Schätzung für die Eingangsvariablen
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 21
Klassen von Iteratoren
Für alle Operationen op R oder R op S versucht man,
R und S möglichst selten zu laden.
•
1-Pass-Iteratoren genügt es, R (und S) einmal zu laden
•
Nested-Loop-Iteratoren lesen R einmal und S mehrfach
•
Mehr-Pass-Iteratoren lesen R (und oft auch S) mehrfach
•
Sort-basierte Iteratoren benutzen Sortierung
•
Index-basierte Iteratoren bauen einen Index auf
•
Hash-basierte Iteratoren bauen eine Hash-Tabelle auf
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 22
11
Merge-Sort
Beispiel 3-Wege Merge-Sort mit 4 Hauptspeicherseiten.
Für jede Folge F von 4 Blöcken aus R tue
{ lade F in den Datenbankpuffer im Hauptspeicher ;
Quicksort ( Datenbankpuffer ) ;
schreibe Datenbankpuffer auf die Platte ;
}
// Platte enthält sortierte Sequenzen der Länge 4
Solange die Platte mehrere sortierte Sequenzen enthält
{ mische jeweils (bis zu) 3 sortierte Sequenzen
in eine längere sortierte Sequenz
}
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 23
1-Pass-Iteratoren für unäre Operatoren
Für Operationen op R
mit op ∈ { load, store, P, S, removeDup, group, sort, hash }
versucht man, mit einem Pass auszukommen,
d.h. die ganze Relation nur 1x im Hauptspeicher zu haben
für load, store, P, S geht das immer
für die anderen Operationen nur,
wenn R in den Hauptspeicher passt
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
Das ist etwas vereinfacht,
weil ggf. noch Platz für
eine Suchstruktur und
Platz für das Sammeln
der Ausgabe sein muss.
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 24
12
Iterator für Datenbankrelation laden
Implementierung der Operationen
Open( )
Öffne Relation, lade erste Seite in den Hauptspeicher
Next( )
gib nächstes Tupel zurück/weiter
Close( )
schließe Relation, gib Hauptspeicher-Ressourcen frei
Size( )
gib Größe der Relation zurück
Cost( )
Anzahl der Blöcke,
die von der Platte in den Hauptspeicher
geladen werden müssen.
Gleiche Implementierung für
auf Platte ausgelagerte Zwischenergebnisse
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 25
1-Pass-Iterator für Projektion
Implementierung der Operationen für P(R)
verkettet mit vorigem Iterator (der R erzeugt)
Open( )
rufe R.Open( ) auf
Ergebnis des Eingangs-Iterators (R)
ist im Hauptspeicher und wird weiter benutzt
Next( )
gib Projektion des aktuellen Tupels R.Next()
zurück/weiter
Close( )
rufe R.Close( ) auf.
Size( )
gib R.Size( ) * Projektionsanteil zurück
Cost( )
0,
weil alles schon im Hauptspeicher ist.
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 26
13
1-Pass-Iterator für Standard-Selektion
Implementierung der Operationen für SB(R)
verkettet mit vorigem Iterator (der R erzeugt)
Open( )
rufe R.Open( ) auf
Ergebnis des Eingangs-Iterators (R)
ist im Hauptspeicher und wird weiter benutzt
Next( )
tuple = R.Next() ;
while ( tuple≠null and not B(tuple) ) { tuple = R.Next(); }
return tuple ;
Close( )
rufe R.Close( ) auf.
Size( )
gib R.Size( ) * Selektivität von SB zurück
Cost( )
0 für Standard-Selektion,
aber R muss bereits im Hauptspeicher liegen
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 27
1-Pass-Iterator für bag-Union
Berechnung von R ∪bag S :
1. übertrage jedes Tupel von R nach Output
Output
R
2. übertrage jedes Tupel von S nach Output
Output
S
cost: 0 , falls R und S bereits im Hauptspeicher sind
Platz-Bedarf: 2 Seiten (R bzw. S) und Output
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 28
14
1-Pass-Iteratoren für binäre Operatoren
Für Operationen R op S
mit op ∈ { ∪set, ∩set, -set, ∩bag, -bag, X, |X| }
und S passt in den Hauptspeicher
baue Suchstruktur für S auf und lese R (und S) nur einmal
R
Output
Suchbaum
Welche Tupel von wo
nach Output übertragen?
S
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 29
1-Pass-Iteratoren für binäre Operatoren
Für Operationen R op S
mit op ∈ { ∪set, ∩set, -set, ∪bag, ∩bag, -bag, X, |X| }
und S passt in den Hauptspeicher
baue Suchstruktur für S auf und lese R (und S) nur einmal
R
Suchbaum
S
Output
∩set : übertrage Tupel t von R nach Output,
falls t nicht in S gefunden wird.
∪set : 1. übertrage Tupel t von R nach Output,
falls t nicht in S gefunden wird.
2. übertrage Tupel aus S nach Output
bei ∩bag zähle Anzahl gleicher Tupel im Suchbaum für S
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 30
15
Übungsaufgabe: 1-Pass-Iteratoren für
binäre Operatoren R-S und S-R
Annahme: S passt in den Hauptspeicher , aber R nicht
Wie implementiert man
1. R –set S ,
2. S –set R ,
3. R –bag S und
4. S –bag R
als 1-Pass-Iteratoren, also so, dass man
mit einer Suchstruktur für S im Hauptspeicher,
einer Eingabeseite für R und
einer Ausgabeseite für Output
auskommt?
Output
R
Datenbanken und Informationssysteme 1
-
Suchbaum
WS 2004 / 05
S
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 31
1-Pass-Iteratoren für binäre Operatoren
Für Operationen R op S
mit op ∈ { ∪set, ∩set, -set, ∪bag, ∩bag, -bag, X, |X| }
und S passt in den Hauptspeicher
baue Suchstruktur für S auf und lese R (und S) nur einmal
R
Suchbaum
S
Output
R-setS : übertrage Tupel t von R nach Output,
falls t nicht in S gefunden wird.
S-setR : 1. lösche Tupel t von R aus S.
2. übertrage Tupel aus S nach Output
bei -bag zähle Anzahl gleicher Tupel im Suchbaum für S
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 32
16
Iterator für 1-Pass-"Nested-Loop-Join",
falls 1 Relation in Hauptspeicher passt
Implementierung der Operation R |X|B S durch Nested-Loop-Join
Falls die kleinere Relation (z.B. S) in den Hauptspeicher passt,
und noch eine Seite für Blöcke aus R frei ist,
und noch eine Seite für die Ergebnissammlung und -ausgabe frei ist:
lade(S) in den Hauptspeicher
solange R noch ungeladene Seiten hat
{ lade die nächste Seite Ri von R in den Hauptspeicher
Ist in Wirklichkeit komplizierter,
for each tuple r in Ri
weil Next() einzelne Tupel weitergibt
for each tuple s in S
if ( r |X|B s ) output (r,s)
}
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 33
Nested-Loop-Iteratoren
Implementierung der Operation R op S durch Nested-Loop-Iteratoren
Lies eine (i.d.R. die größere) Relation R einmal
und die andere Relation (i.d.R. die kleiner) S mehrfach,
ggf. im Zickzack-Modus
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 34
17
Naiver Iterator für Nested-Loop-Join, falls
keine Relation in den Hauptspeicher passt
Implementierung der Operation R |X|B S
Die naive Implementierung
R wird nur einmal gelesen
for each tuple r in R
for each tuple s in S
if ( r |X|B s ) output (r,s)
Ist in Wirklichkeit komplizierter,
weil Next() einzelne Tupel weitergibt
produziert zu viele Seitenfehler,
weil pro Tupel r aus R ganz S einmal geladen wird
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 35
Iterator für Nested-Loop-Join, falls
keine Relation in den Hauptspeicher passt
Implementierung der Operation für R |X|B S
Teile Hauptspeicher auf in k Seiten für S und m-k Seiten für R
K = Folge der nächsten k Seiten aus S
M = Folge der nächsten m-k Seiten aus R
R wird nur einmal gelesen
for each M in R
for each K in S
Ist in Wirklichkeit komplizierter,
for each tuple r in M
weil Next() einzelne Tupel weitergibt
for each tuple s in K
if ( r |X|B s ) output (r,s)
produziert weniger Seitenfehler,
weil nur pro Seiten-Folge M aus R ganz S einmal geladen wird
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 36
18
Zickzack-Iterator für Nested-Loop-Join, falls
keine Relation in den Hauptspeicher passt
Blöcke aus S
1.
2.
3.
4.
5.
for each page Ri in R
solange nicht ganz S gelesen ist
lies je k Seiten K aus S im Zickzack-Verfahren
for each tuple r in Ri
for each tuple s in K
if ( r |X|B s ) output (r,s)
produziert noch weniger Seitenfehler, weil
Seiten "am Rand von S" seltener geladen werden
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
k
k Seiten aus S
Ri
Ausgabe
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 37
Sort-basierte unäre Iteratoren
1. Sortieren R
( das geht mit N-Wege-Merge-Sort )
2. führe eigentliche Zieloperation durch,
z.B. Duplikateliminierung
z.B. Gruppierung
(das geht mit jeweils einem Scan der sortierten Relation)
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 38
19
Sort-basierte Iteratoren
Sortieren R oder S oder (R und S)
Sort-Merge-Join
sotiere R und S nach Join-Attributen
Merge-Lauf:
wähle alle Paare mit gleichen Join-Attributwerten
(
Tafel )
Sort-basierte Operationen für ∪, ∩, sortiere R und S , zähle mehrfach vorkommende Tupel
Merge-Lauf
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 39
Index-basierte Iteratoren
bauen einen Index für R oder für S oder für R und S auf
beim Primär-Index R zuerst sortieren nach Index-Kriterium
dann B+-Baum aufbauen
beim Sekundär-Index genügt:
aus Primärdaten Sekundärschlüssel extrahieren
+ Paare ( Sekundärschlüssel, Referenz auf Daten-Block)
in einen B+-Baum eintragen
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 40
20
Iterator für Index-Selektion
Implementierung der Operationen für SB(R) , z.B. für key>66
Open( )
Abstieg im Index (z.B. B+-Baum) zu der ersten Seite,
die die Selektionsbedingung erfüllt
Next( )
über den Index Zugriff auf das nächste Tupel
(i.d.R. auf einer anderen Seite)
Stoppe, wenn Index-Schlüssel zu groß werden
Close( )
rufe R.Close( ) auf und schließe den Index.
Size( )
gib R.Size( ) * Selektivität von SB zurück
Cost( )
begrenzt durch
Anzahl der Ergebnistupel + Indextiefe +
Selektivität ( SB ) Indexblattanzahl
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 41
Iteratoren für Index-Join
Index für R, Scan von S
Für jedes Tupel s aus S
benutze Index von R,
um Join-Partner r aus R zu suchen
falls join (r,s) output (r,s)
Index für R und Index für S
benutze Index von R und Index von S für einen Merge-Lauf
(wie beim Sort-Merge-Join)
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 42
21
Hash-basierte Iteratoren
bauen Hash-Tabelle für R oder für S oder für R und S auf
Ziel:
große Relationen solange durch Hashfunktion partitionieren,
bis (einige) Teile in den Hauptspeicher passen
Unären Operatoren auf Partitionen im Hauptspeicher durchführen
z.B. Duplikateliminierung
Binäre Operatoren auf Partitionen durchführen, die zueinander passen,
dabei:
andere Iteratoren verwenden
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 43
Hash-Join R |X|a=b S
baut (ggf. wiederholt) Hash-Tabelle für (Partitionen von) R und S auf
Split( R, S )
{ wähle neue Hash-Funktion h für R.a und benutze h auch für S.b
zerlege R und S mit h in mehrere Behälter R1,…,Rn und S1,…,Sn
für jedes Behälter-Paar (Ri,Si):
falls (mind. ein Behälter (Ri oder Si) passt in Hauptspeicher)
Nested-Loop-Join( Ri, Si ) ;
sonst Split( Ri, Si )
}
Bsp. Tafel: Männer |X|alter=alter Frauen
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 44
22
Hash-Join R |X|a=b S
Warum wird wiederholt mit Hash-Funktion partitioniert ?
•
Alles auf einmal partitionieren,
würde evtl. zu viele zu kleine Partitionen bilden
(fast leere Behälter brauchen zuviel Platz)
•
Grad der Partitionierung (Verzweigungsfaktor)
ist limitiert durch Anzahl der Seiten,
die im Hauptspeicher dafür zur Verfügung stehen
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 45
Zusammenfassung Iterator-Klassen
Für alle Operationen op R oder R op S versucht man,
R und S möglichst selten zu laden.
•
1-Pass-Iteratoren genügt es, R (und S) einmal zu laden
•
Nested-Loop-Iteratoren lesen R einmal und S mehrfach
•
Mehr-Pass-Iteratoren lesen R (und oft auch S) mehrfach
•
Sort-basierte Iteratoren benutzen Sortierung
•
Index-basierte Iteratoren bauen einen Index auf
•
Hash-basierte Iteratoren bauen eine Hash-Tabelle auf
Datenbanken und Informationssysteme 1
-
WS 2004 / 05
- Prof. Dr. Böttcher - S. Anfrageoptimierung / 46
23