Transcript Kapitel 13 - Institute for Program Structures and Data

Universität
Karlsruhe (TH)
Kapitel 9
Relationale Operatoren
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
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
‹#›
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 9
‹#›
Kapitel 9.1
Kostenmodell
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Aufgabe
‹#›
Ausgangspunkt
 Verarbeitet werden Materialisierungen, nicht externe
Relationen.
Materialisierungen haben den Charakter von Relationen.
 Daher lassen sich auf sie die relationalen Operatoren
anwenden.

Aufgabe
 Zur Implementierung jeden relationalen Operators existieren
mehrere Algorithmen.


Bestimmung der jeweiligen Kostenfunktion.
Bei der Bearbeitung einer Anfrage muss ein kostengünstiger
Algorithmus ausgewählt werden:

Berücksichtigung von Einflussfaktoren bei der Auswahl.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Elemente der Kostenfunktion
‹#›
Indexinformationen
algebraischer Ausdruck
Ballungsinformationen
Kostenfunktion
Ausführungskosten
Bestimmen die Anzahl der Zugriffe
auf den Hintergrundspeicher
Kardinalitäten
1. Zugriffskosten auf Hintergrundspeicher


für Basisrelationen
aber auch für große Zwischenergebnisse
2. Speicherkosten der Zwischenergebnisse (Ergebniskardinalität!)
3. CPU-Kosten für die Berechnung (wird i.Allg. vernachlässigt)
4. Kommunikationskosten

Spielen erst bei der verteilten Anfrageoptimierung eine Rolle
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Einflussfaktoren
‹#›
Indexinformationen
algebraischer Ausdruck
Ballungsinformationen
Kostenmodell
Ausführungskosten
vorhanden / nicht vorhanden
Kardinalitäten



Liefert einen Multiplikator: Größe der einzelnen Basis- oder Zwischenrelation.
Die Größe jeder Zwischenrelation muss daher zuvor abgeschätzt werden.
Charakterisierung einer Operation dann durch ihre Selektivität
Selektivität =
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
erwartete Anzahl der Ergebnistupel
Zahl der Basistupel d. Operation
DBI 9
Selektivität
‹#›
Selektivitätsabschätzung

Da nahezu alle relationalen Operatoren auf einem Vergleich
von Attributwerten basieren, beruht die Abschätzung auf der
Kenntnis der Attributwertverteilungen.

Im Allgemeinen muss Aufwand getrieben werden, um die
Verteilung der Attributwerte zu bestimmen.

Verfahren:

Parametrische Verfahren
ggf.
 Parameterfreie (Histogramm-) Verfahren Stichprobenverfahren
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Parametrische Verfahren (1)
‹#›


Approximieren die Verteilung durch mathematische
Wahrscheinlichkeitsverteilung, z.B. Normalverteilung

Knappe Beschreibung durch wenige Parameter

Nur diese müssen ermittelt werden, z.B. Minimum und
Maximum bei Gleichverteilung, Erwartungswert und Varianz
bei Normalverteilung
Erweiterung auf mehrere Dimensionen zur Erfassung
korrelierter Attribute, wie beispielsweise „Position“ und
„Einkommen“ bei Angestellten einer Firma


z.B. multivariate Normalverteilung, bestimmt durch
Erwartungswertvektor und Kovarianzmatrix
Nachteile:

Art der Verteilung darf sich nicht im Laufe der Zeit ändern

mangelnde Universalität
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Parametrische Verfahren (2)
‹#›
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Histogrammverfahren (1)
‹#›

Approximation durch Stufenfunktion.

Äquidistante Unterteilung, für jedes Intervall wird die Anzahl
der Attributwerte bestimmt, die hineinfallen. Innerhalb der
Intervalle wird Gleichverteilung angenommen.

Die Erweiterung des Histogramm-Verfahrens auf mehrere
Dimensionen zur Erfassung von Korrelationen ist leicht
möglich.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Histogrammverfahren (2)
‹#›
relative schlechte
Abschätzung (große
Abweichung Verteilung
zu Histogramm).


Fehler hängt nicht nur von der Auflösung ab, sondern auch
von der Verteilung selbst, die ja unbekannt ist.
Abhilfe durch nicht äquidistante Unterteilung des
Wertebereichs: Gleiche Höhenintervalle statt gleicher Breite.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Histogrammverfahren (3)

Vorzüge generell:



‹#›
Universalität: Keine Voraussetzungen bzgl.
Wahrscheinlichkeitsverteilung
Verbreitetes Verfahren als Voraussetzung für die
Anfrageoptimierung
Nachteile generell:
Höherer Speicheraufwand
 Schätzung der Selektivität aufwendiger, da auf mehr Daten
zugegriffen werden muss
 Erstellung bei gleichen Höhenintervallen ist teurer, da die
Attributwerte sortiert werden müssen
 Neuerstellung/Neuberechnung des Histogramms in
periodischen Zeitabständen erforderlich


Insbesondere Fortschreiben unter Beibehalten der gleichen
Höhenintervalle schwer möglich
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Stichprobenverfahren (Sampling)
‹#›


Aufwandsverringerung: Repräsentative Stichprobe der
Datenbasis zur

Parameterschätzung bei parametrischen Verfahren

Histogrammberechnung ohne Durchsuchen der gesamten
Datenbasis, wichtig besonders bei nicht-äquidistanter
Unterteilung.
Stichprobenumfang hängt nur von der gewünschten
Genauigkeit ab, nicht von der Größe der Datenbasis.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Datenwörterbuch
‹#›


Verwaltung der Informationen für die Parameter

Kardinalität der Relationen

Größe der Tupel

Ballungseigenschaften der Daten

Indexstrukturen zur Unterstützung des Zugriffs

Verteilungen / Histogramme
Periodisches Fortschreiben z.B.

in Zeiten niedriger Systemlast

oder parallel zu den Anwendungen
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.2
Unäre Operatoren
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenparameter
‹#›
Parameter für die Kostenfunktionen
Parameter
T(R)
B(R)
Bf(R)
X(Idx)
B(Idx)
V(R,Attr)
S(R,Attr)
Bedeutung
Kardinalität (Tupelzahl) der Relation R
Anzahl der Seiten, auf denen Tupel aus R liegen
blocking factor (Anzahl Tupel pro Seite für R)
Höhe des Indexes (B-Baum) Idx
Anzahl der Stellvertreter-Knoten des Indexes
(nur bei B*-Baum)
Anzahl verschiedener Werte für die Attributfolge
Attr der Relation R
Selektivität einer Attributfolge
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Exact Match (1)
‹#›
S1 Lineare Suche (Ballung nicht in Sortierreihenfolge)
Kosten (Zahl der Hintergrundspeicherzugriffe):
a) Selektion auf Nicht-Schlüssel-Attribut
CS1a = B(R)
b) Selektion auf Schlüssel-Attribut
CS1b = B(R)/2
Relation Employee: T(E) = 10.000, B(E) = 2.000, Bf(E) = 5
SSN=007(Employee)
CS1b = B(E) / 2 = 1000
DNO=5(Employee)
CS1a = B(E) = 2000
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Exact Match (2)
‹#›
S2 Binäre Suche (Ballung gemäß Sortierung nach dem
Suchkriterium)
Kosten (Zahl der Hintergrundspeicherzugriffe):
a) Selektion auf Nicht-Schlüssel-Attribut:
CS2a = log2 B(R) +  (T(R)S(R,Attr))/ Bf(R)  1
b) Selektion auf Schlüssel-Attribut):
CS2b = log2 B(R)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Exact Match (3)
‹#›
S3 Nutzung eines Primärindexes, um ein einziges Tupel zu
lesen
Kosten (Zahl der Hintergrundspeicherzugriffe):
a) B*-Baum:
CS3a = X(Idx) + 2
b) B+-Baum (Clusterindex): CS3b = X(Idx) + 1
c) Hash-Index:
CS3c 1
Relation Employee: T(E) = 10.000,
SSN=007(Employee)
Primärindex auf SSN mit X(Idx) = 4: CS3a = X(Idx) + 2 = 6
Clusterindex auf SSN mit X(Idx) = 4: CS3b = X(Idx) + 1 = 5
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Exact Match (4)
‹#›
S4 Nutzung eines Sekundärindexes, um Tupel mit gleichem
Wert zu lesen
Kosten (Zahl der Hintergrundspeicherzugriffe):
a) B*-Baum: CS4a = X(Idx) + 1 + T(R)S(R,Attr)
b) B+-Baum(Clusterindex):
CS4b = X(Idx) +  ((T(R)S(R,Attr)) / Bf(R)) 
Relation Employee: T(E) = 10.000, B(E) = 2.000, Bf(E) = 5
Sekundärindex auf DNO mit X(Idx) = 2,
S(E,DNO) = 1/125 (125 verschiedene DNO-Werte)
DNO=5(Employee)
CS4a = X(Idx) +1 + T(E)S(E,DNO) = 3 + 80 = 83
CS4b = X(Idx) +(T(E)S(E,DNO))/Bf(E) = 2 + 16 = 18
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Range Query (1)
‹#›
S5 Lineare Suche (Ballung nicht in Sortierreihenfolge)
Kosten (Zahl der Hintergrundspeicherzugriffe):
a) Range Query:
CS5a = B(R)
Relation Employee: T(E) = 10.000, B(E) = 2.000, Bf(E) = 5
DNO >5(Employee)
CS5a = B(E) = 2000
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Range Query (2)
S6 Nutzung eines Indexes (Vergleichsprädikat <, >, , )
‹#›
Kosten (Zahl der Hintergrundspeicherzugriffe):
a) Sekundärindex (also keine Ballung von sortierten Tupeln):
Annahme: die Hälfte der Tupel erfüllt das
Selektionsprädikat:
CS6a = X(Idx) + B(Idx)/2 + T(R)/2
b) Clusterindex (Ballung von sortierten Tupeln): Sehr grobe
Abschätzung mit Annahme wie bei a):
CS6b = X(Idx) + B(R)/2
Relation Employee: T(E) = 10.000, B(E) = 2.000, Bf(E) = 5
Sekundärindex auf DNO mit X(Idx) = 2, B(Idx) = 4
DNO>5(Employee)
CS6a = X(Idx) + B(Idx)/2 + T(E)/2 = 5004
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Konjunktive Suchprädikate (1)
‹#›
S7
a) Vorauswahl der Tupel gemäß eines der Glieder der
Konjunktion, die durch S2, S3, S4, S6 unterstützt wird,
dann Zugriff auf die vorausgewählten Tupel und Test des
verbleibenden Teils des Selektionsprädikats.
b) Nutzung einer mehrdimensionalen Indexstruktur.
c) Schnittmengenbildung von ID-Listen aus mehreren
Indexen gemäß S3, S4 oder S6, je nach anwendbarem
Index.
d) Lineare Suche.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Konjunktive Suchprädikate (2)
DNO=5 and Salary>30000 and Sex=F Employee
‹#›
Relation Employee: T(E) = 10.000, B(E) = 2.000, Bf(E) = 5
Clusterindex auf Salary mit X(Idx) = 3
Sekundärindex auf DNO mit X(Idx) = 2,
S(E,DNO) = 1 / 125 (125 verschiedene DNO-Werte)
Sekundärindex auf Sex mit X(Idx) = 1,
Kann weit besser
S(E,Sex) = 1 / 2 (2 verschiedene Sex-Werte)
sein  Verteilung?
CS7d = B(E) = 2.000
S7a: Vorauswahl nach DNO=5:
CS4a = X(Idx) +1 + T(E)S(E,DNO) = 3 + 80 = 83, restliche
Bedingungen durch Inspektion der ausgewählten Tupel
S7a: Vorauswahl nach Salary>30.000:
CS6b = X(Idx) + B(E)/2 = 3 + 2000 / 2 = 1003, restliche
Bedingungen durch Inspektion der ausgewählten Tupel
S7a: Vorauswahl nach Sex=F:
CS4a = X(Idx) +1 + T(E)S(E,DNO) = 2 + 5000 = 5002,
restl. Bedingungen durch Inspektion d. ausgewählten Tupel
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Disjunktive Suchprädikate
‹#›



Disjunktive Selektionsprädikate können kaum unterstützt
werden
Beispiel: DNO = 5 or Salary > 30000 or Sex = F Employee
Selbst wenn es Indexe auf Salary und DNO gibt, muss man
dennoch alle Tupel der Relation (physisch) lesen, um „Sex
= F“ auszuwerten.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Selektion: Selektivität
sel(p,R) = p(R) / T(R)
Empfohlene Schätzung bei Annahme Gleichverteilung:
 A Schlüssel von R: Unabhängig von Verteilung
sel(A=c,R) = 1/T(R)
 V(R,A) verschiedene Werte für A:
sel(A=c,R) = 1/V(R,A)
 Vergleich arithmetisch
sel(A<c,R) = (c-Amin)/(Amax-Amin)
sel(A>c,R) = (Amax-c)/(Amax-Amin)
 Vergleich nicht-arithmetisch
sel(A<c,R) = sel(A>c,R) = 1/3
‹#›
Schreibaufwand: (T(R)  sel(p,R))/Bf(R) .
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Projektion mit Duplikateliminierung (1)
Fall 1: A(R) mit R sortiert nach A.
 Alle r  A(R) werden in das Ergebnis aufgenommen, für
die gilt r  previous(r).

‹#›
Aufwand: CP1a = B(R)
Fall 2: A(R) mit Index auf A.
 Nutze Sortierung durch Index.

Aufwand: CP2 = X(Idx) + B(Idx) + T(R)
Fall 3: A(R) mit R nicht sortiert nach A.
 A(R) wird zunächst nach A sortiert, dann wie nach Fall 1.

Aufwand: CP3 = B(R) + B(R)logm-1(B(R)/m)
Schreibaufwand: (höchstens) B(R).
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Projektion mit Duplikateliminierung (2)
‹#›
Fall 4: A(R) mit R nicht sortiert nach A.
Nächstes Tupel lesen,
auf A projizieren:
R
Nachsehen ob A(R)
schon im Puffer
m-1 Puffer
A(R) schon
gesehen?
nein
A(R) in Puffer und
Ausgabe schreiben
Ausgabe-Puffer
Wichtig für die Performanz:
 A(R) sollte in den Puffer passen.
 Schnelle Zugriffsstruktur, z.B. Hashing.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Projektion: Selektivität
‹#›
Empfohlene Schätzung:

Ohne Duplikateliminierung
sel(A,R) = A(R) / T(R)
sel(A,R) = T(R)/T(R) = 1

Mit Duplikateliminierung:
sel(A,R) = A(R) / T(R)
n
sel(A,R) = min (T(R),  V(R,Ai) mit A=(A1, A2, ..., An)
i 1
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Gruppierung (1)
‹#›
Fall 1: A(R) mit R sortiert nach A.

Mit jedem Wechsel von A(R) wird eine Gruppe
abgeschlossen und ein neue begonnen.

Das Ergebnis, gegebenenfalls zuerst aggregiert, wird
geschrieben.

Aufwand: CG1 = B(R)
Fall 2: A(R) mit Index auf A.
 Nutze Sortierung durch Index, ansonsten wie oben.

Aufwand: CG2 = X(Idx) + B(Idx) + T(R)
Schreibaufwand: B(R).
Schreibaufwand und Hauptspeicherbedarf geringer
bei Aggregierung.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Gruppierung (2)
Fall 3: A(R) mit R nicht sortiert nach A.
 A(R) wird zunächst nach A sortiert, dann wie nach Fall 1.

‹#›
Aufwand: CG3 = B(R) + B(R)logm-1(B(R)/m)
Fall 4: A(R) mit R nicht sortiert nach A.

Partitionierung: Anwendung einer Hashfunktion auf A.

Aufwand hängt von Güte der Partitionierung ab sowie von
der Notwendigkeit, die Partitionen auf den
Hintergrundspeicher zu schreiben.
Schreibaufwand: B(R).
Schreibaufwand und Hauptspeicherbedarf geringer
bei Aggregierung.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Gruppierung (3)
‹#›
Empfohlene Schätzung:
sel(A,R) = A(R) / T(R)
n
sel(A,R) = min (T(R),  V(R,Ai) mit A=(A1, A2, ..., An)
i 1
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.3
Gleich-Verbindung (Equi-Join)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenparameter
Equi-Join: R ⋈A=B S mit Spezialfall natural join
‹#›
Parameter für die Kostenfunktionen
Parameter
T(R)
B(R)
Bf(R)
X(Idx)
B(Idx)
V(R,Attr)
S(R,Attr)
Bedeutung
Kardinalität (Tupelzahl) der Relation R
Anzahl der Seiten, auf denen Tupel aus R liegen
blocking factor (Anzahl Tupel pro Seite für R)
Höhe des Indexes (B-Baum) Idx
Anzahl der Stellvertreter-Knoten des Indexes
(nur bei B*-Baum)
Anzahl verschiedener Werte für die Attributfolge
Attr der Relation R
Selektivität einer Attributfolge
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.3.1
Nested-Loops-Methoden
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grundmuster
foreach r  R do
foreach s  S do
if s.B = r.A then Res := Res  (r  s)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
‹#›
DBI 9
Block-Nested-Loops-Verbindung (1)
‹#›


Geg.: Puffergröße m in Blöcken zum Lesen der Relationen sowie B(R)
und B(S)
Dann


Nutze m-1 Pufferblöcke für die äußere Schleife (z.B. zum Lesen von R)
Nutze 1 Pufferblock für die innere Schleife (z.B. zum Lesen von S)
1
m-1
R
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
S
DBI 9
Block-Nested-Loops-Verbindung (2)
‹#›


Geg.: Puffergröße m in Blöcken zum Lesen der Relationen sowie B(R)
und B(S)
Dann



Nutze m-1 Pufferblöcke für die äußere Schleife (z.B. zum Lesen von R)
Nutze 1 Pufferblock für die innere Schleife (z.B. zum Lesen von S)
Algorithmus, o.B.d.A. hier: B(R) ist Vielfaches von m-1
for (i = 0; i < B(R)/(m-1), i++) {
Lese Blöcke i(m-1) bis (i+1)(m-1)-1 von R
for (int j = 0, j < B(S), j++) {
Lese Block j von S;
Führe nach Grundmuster Join
bzgl. zuletzt gelesener Blöcke von R und
bzgl. zuletzt gelesenem Block von S
im Hauptspeicher (also auf Puffer) durch
}
}
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenfunktion (1)
‹#›

Es werden in B(R)/(m-1) Abschnitten insgesamt B(R)
Seiten in der äußeren Schleife eingelesen
In jedem Abschnitt werden B(S) Seiten neu eingelesen

Kosten:

CBNL = B(R) + (B(R)/(m-1)  B(S)) +
(T(R)  T(S)  sel(R⋈S))/Bf(R⋈S)
Lesen der Relationen in
den Puffer
Schreiben des Join-Resultats auf
die Festplatte

Bf(R⋈S): Blockungsfaktor der Join-Tupel

sel(R⋈S): Selektivität des Ergebnisses
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Block-Nested-Loops-Verbindung (3)
‹#›

Für den Fall zweier Relationen A und B mit B(A) >> B(B)
setze R := B (also die von der Seitenzahl her kleinere
Relation ist die äußere Relation).

Bessere Chance dass B(R)/(m-1) klein bleibt (Extremfall
B(R) = (m-1) ).
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenfunktion (2)
‹#›
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
Department (Dname, Dnumber, MgrSSN, MgrStartDate)
Anfrage: Employee ⋈DNO = Dnumber Department
Annahmen:
 Employee wie gehabt (T(E) = 10000; B(E) = 2000)
 Department (T(D) = 125, B(D) = 13)
 Join-Selektivität ist sel(E⋈D)  1/T(D) (= 1/125)
weil Dnumber Schlüssel von Department ist

Bf(E⋈D) sei 4
Employee als äußere Schleife
CBNL = B(E) + B(D)   B(E)/(m-1)  + (T(E)  T(D)  sel(E⋈D)) / Bf(E⋈D)
m=2:
CBNL = 2.000 + 13  2.000 + (10.000  125  (1/125))/4) = 30.500
m=6:
CBNL = 2.000 + 13  400 + (10.000  125  (1/125))/4) = 9.700
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenfunktion (3)
‹#›
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
Department (Dname, Dnumber, MgrSSN, MgrStartDate)
Anfrage: Employee ⋈DNO = Dnumber Department
Annahmen:
 Employee wie gehabt (T(E) = 10000; B(E) = 2000)
 Department (T(D) = 125, B(D) = 13)
 Join-Selektivität ist sel(E⋈D)  1/T(D) (= 1/125)
weil Dnumber Schlüssel von Department ist

Bf(E⋈D) sei 4
m=6:
Employee als äußere Schleife
CBNL = B(E) + B(D)   B(E)/(m-1)  + (T(E)  T(D)  sel(E⋈D)) / Bf(E⋈D)
CBNL = 2.000 + 13  400 + (10.000  125  (1/125))/4) = 9.700
Department als äußere Schleife
CBNL = B(D) + B(E)   B(D)/(m-1)  + (T(E)  T(D)  sel(E⋈D)) / Bf(E⋈D)
CBNL = 13 + 2.000  3 + (10.000  125  (1/125))/4) = 8.513
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.3.2
Misch-Methoden
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Sort-Merge-Join

Schritt 1: Sortierungen
1. R muss nach A und
2. S nach B sortiert werden

‹#›
Entfällt falls R und S
bereits sortiert
Schritt 2: Mischen (Merge-Join)

Falls A oder B Schlüsselattribut ist, wird jedes Tupel in R und
S nur genau einmal gelesen
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenfunktionen (1)
‹#›
Merge-Join
Falls für alle c A=c(R) oder A=c(S) klein, müssen R und
S nur einmal eingelesen werden:
CMJ = B(R) + B(S) + (T(R)  T(S)  sel(R⋈S)) / Bf(R⋈S)
Gilt insbesondere, wenn Join-Attribut in einer der beiden
Relationen eindeutiger Schlüssel.
Sort-Merge-Join
CSMJ: Für jede zu sortierende Relation kommt nach Kap.8
Sortieraufwand hinzu (hier für R):
2B(R) + 2B(R)logm-1(B(R)/m) = 2B(R)(1+logm-1(B(R)/m))
(m: Mischgrad)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenfunktion (2)
‹#›
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
Department (Dname, Dnumber, MgrSSN, MgrStartDate)
Anfrage: Employee ⋈DNO = Dnumber Department
 Employee wie gehabt (T(E) = 10000; B(E) = 2000)
 Department (T(D) = 125, B(D) = 13)
 Join-Selektivität ist sel(E⋈D)  1/T(D) (= 1/125)
weil Dnumber Schlüssel von Department ist

Bf(E⋈D) sei 4
m=6:
Employee und Department bereits sortiert
CMJ = B(E) + B(D) + (T(E)  T(D)  sel(E⋈D)) / Bf(E⋈D)
CMJ = 2000 + 13 + (10.000  125  (1/125))/4) = 4.513
Employee und Department noch zu sortieren
CSMJ = CMJ + 2B(E)(1+log5(B(E)/6)) + 2B(D)(1+log5(B(D)/6))
CSMJ  25.000
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.3.3
Index-Methoden
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grundmuster


foreach r  R do
foreach s  S[B=r.A] do
Res := Res  (r  s)
beim Durchlauf von R werden jeweils in S die zu R.A
korrespondierenden Tupel gelesen
dazu ist ein Index auf S.B erforderlich
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
‹#›
DBI 9
Kostenfunktionen (1)
‹#›

Sekundärindex auf S.B:
CIJa = B(R) + T(R)  (X(IdxB) + 1 + T(S)S(S,B))
+ (T(R)  T(S)  sel(R⋈S)) / Bf(R⋈S)

Clusterindex auf S.B:
CIJb = B(R) + T(R)  (X(IdxB) + (T(S)S(S,B))/Bf(S) )
+ (T(R)  T(S)  sel(R⋈S)) / Bf(R⋈S)

Primärindex auf S.B (B ist Schlüssel):
CIJc = B(R) + T(R)  (X(IdxB) + 2)
+ (T(R)  T(S)  sel(R⋈S)) / Bf(R⋈S)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Kostenfunktionen (2)
Employee (Fname, Lname, SSN, BDate, Address, Sex, Salary, SuperSSN, DNO)
Department (Dname, Dnumber, MgrSSN, MgrStartDate)
‹#›
Anfrage: Employee ⋈DNO = Dnumber Department
 Employee (T(E) = 10000; B(E) = 2000)
Sekundärindex auf DNO mit X(Idx) = 2
 S(E,DNO) = 1 / 125 (125 verschiedene DNO-Werte)


Department (T(D) = 125, B(D) = 13)



Primärindex auf Dnumber in Department mit X(Idx) = 1
Join-Selektivität ist sel(E⋈D)  1/T(D) (= 1/125)
Bf(E⋈D) = 4
Employee als äußere Schleife
CIJc = B(E) + T(E)(X(IdxDnum) + 2) = 2000 + 10.0003 = 30.200
Department als äußere Schleife
CIJa = B(D) + T(D)  (X(IdxDNO)+1+T(E)S(E,DNO)) = 13+12583 = 10.388
Schreibaufwand
(T(E)  T(D)  sel(E⋈D)) / Bf(E⋈D) = (10.000  125  (1/125))/4) = 2.500
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.3.4
Hash-Methoden
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Simple Hash Join (1)


‹#›
Partitioniere Relation R in R1, R2, ..., Rp gemäß
Hashfunktion h so dass für alle Tupel r  Ri h(r.A)  Hi.
Führe Scan auf Relation S aus, wende auf jedes Tupel s 
S die Hashfunktion h an. Fällt h(s.B) in den gewählten
Bereich Hi, suche dort einen r-Partner
Grundmuster:
repeat
begin
Build-Phase
while memory not exhausted do
insert next(R) into partition[h(next(R).A)];
foreach s  S do
Probe-Phase
foreach r  partition[h(s.B)] do
if r.A = s.B Res := Res  (r  s)
end
until R exhausted
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Simple Hash Join (2)
‹#›
Problem: R passt nicht in den Hauptspeicher.



Schritt 1 (Build): Führe Scan auf kleinere Relation R aus, wende
auf jedes Tupel r  R die Hashfunktion h an. Fällt h(r.A) in den
gewählten Bereich Hi (anfangs i=1), trage r in den Arbeitsbereich
ein, andernfalls in einen Puffer für R. Falls Puffer überläuft,
schreibe seinen Inhalt in eine Datei für „übergangene“ r-Tupel.
Schritt 2 (Probe): Führe Scan auf Relation S aus, wende auf
jedes Tupel s  S die Hashfunktion h an. Fällt h(s.B) in den
gewählten Bereich Hi, suche im zugehörigen Arbeitsbereich einen
r-Partner. Falls erfolgreich, bilde Ergebnistupel, andernfalls
speichere s in Puffer für S. Falls Puffer überläuft, schreibe seinen
Inhalt in eine Datei für „übergangene“ s-Tupel.
Schritt 3: Wiederhole die beiden Schritte mit den übergangenen
Tupeln so lange, bis R erschöpft ist.
Voraussetzung: Jede Partition passt in den Hauptspeicher.
Empfehlung: Wähle h so dass alle Partitionen etwa gleich groß
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Simple Hash Join (3)
‹#›
1.Iteration:
R
Amin
Amax
H1
RRest
SRest
Bmin
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
S
Bmax
DBI 9
Hybrid Hash Join (1)
Abwandlung des Simple Hash Join, indem statt der Dateien
der übergangenen Tupel die Partitionen für R und S
komplettiert werden.
 Fange so an, als wenn der Build-Input R vollständig in den
Hauptspeicher passen würde.
 Sollte sich dies als zu optimistisch herausstellen, verdränge
eine Partition nach der anderen aus dem Hauptspeicher.




‹#›
Mindestens eine Partition wird aber im Hauptspeicher
verbleiben.
Danach beginnt die Probe-Phase mit der Relation S.
Jedes Tupel aus S, dessen potentielle Join-Partner im
Hauptspeicher sind, wird sogleich verarbeitet, andernfalls
wird es in die entsprechenden Partitionen eingeordnet.
Danach müssen nur noch die verbliebenen Partitionen
zusammengeführt werden.
Besonders interessant, wenn der Build-Input knapp größer als der
Hauptspeicher ist.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Hybrid Hash Join (2)
‹#›
Zwischenstand Build(R)
Hashtabelle
P1
P2
P3
S
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R
DBI 9
Hybrid Hash Join (2)
‹#›
Verdrängen einer Partitition
Hashtabelle
P1
P2
P3
S
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R
DBI 9
Hybrid Hash Join (2)
‹#›
Verdrängen einer Partitition
Hashtabelle
P1
P2
P3
S
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R
DBI 9
Hybrid Hash Join (2)
‹#›
Zwischenstand während Partitionierung von S
Hashtabelle
Partition
h(S.B)
P2
P3
P2
P3
S
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
probe
Wenn s zur
ersten Partition
gehört
DBI 9
Grace Hash Join (1)
‹#›
Grundgedanke:
Entwicklung aus dem Hybrid Hash Join heraus, indem auch
die Verzahnung bei den im Hauptspeicher gehaltenen
Partitionen zugunsten eines expliziten Aufbaus aller
Partitionierungen nach hinten verschoben wird.
 Strikte Trennung von Partitionierung und
Ergebnisberechnung.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (2)
‹#›
Partitionierung 1: Partitioniere kleinere Relation, evtl.
wiederholt, gemäß Hashfunktionen hi bis jede Partition in den
Hauptspeicher passt.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (3)
‹#›
Partitionierung 2: Partitioniere größere Relation, evtl.
wiederholt, gemäß der selben Hashfunktionen hi. Partitionen
müssen nicht in den Hauptspeicher passen.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (4)
‹#›
Hole der Reihe nach jede Build-Partition in den
Hauptspeicher, organisiere sie als Hashtabelle, vergleiche
entsprechende Probe-Partition mit Hashtabelle.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (5)
‹#›
Lies S
Schreibe S
Lies R,S
Schreibe R
Lies R
Schwierige analytische Kostenabschätzung („Güte“
der Hashfunktion geht ein). Grob:
CGHJ = 3  (B(R) + B(S)) + ((jsT(R)T(S))/BfRS )
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (6)
‹#›
P1
P1
Partition
h(S.A)
P2
P3
receive
Partition
h(R.A)
P2
P3
receive
Send
Send
S
R
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (7)
‹#›
P1
P2
P3
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
build
Lade Blöcke von P1
Hashtabelle
Partition
h(S.A)
probe
P1
P2
P3
DBI 9
Grace Hash Join (8)
‹#›
Beispiel:
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Grace Hash Join (9)
‹#›
2 Varianten:
1. TIDs werden in die Partitionen gehasht
 passt leichter in den Hauptspeicher
2. Vollständige Tupel werden in die Partitionen gehasht
Anmerkung:

Grace Hash Join (und Hybrid Hash Join) lassen sich gut
parallelisieren.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.3.5
Abschätzungen
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Aufwandsvergleich
‹#›
Nested-Loop
Elementvergleich
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
Merge
Hash
Elementvergleich, der zu Join-Ergebnis führt
DBI 9
Join-Selektivität (1)
‹#›
js = (R ⋈p S) / (R  S) = (R ⋈p S) / (R S)

Sonderfälle:

p = true: js = 1

keine 2 Tupel aus R und S erfüllen p: js = 0

p  (R.A=S.B)

falls A Schlüssel von R ist: Join erfolgt über eine
Fremdschlüsselbeziehung mit Fremdschlüssel in S  Jedes
Tupel in S kann sich mit höchstens einem Tupel aus R verbinden
 T(R ⋈p S)  T(S) 
sel(R ⋈p S) = T(R ⋈p S) / (T(R)  T(S))
≤ T(S) / (T(R)  T(S))

≤ 1 / T(R)
falls B Schlüssel von S ist: entsprechend js  1/T(S)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Join-Selektivität (2)
‹#›
js = (R ⋈p S) / (R  S) = (R ⋈p S) / (R S)

Empfohlene Abschätzung bei p  (R.A=S.B):

A, B ein-attributig
T(R ⋈p S) = (T(R)T(S)) / max( V(R,A),V(S,B) )
js = 1 / max( V(R,A),V(S,B)

A, B mehr-attributig (Spezialfall 2 Attribute):
T(R ⋈p S) = (T(R)T(S)) /
( (max(V(R,A1),V(S,B1))  (max(V(R,A2),V(S,B2)) )
js = 1 / ( (max(V(R,A1),V(S,B1))  (max(V(R,A2),V(S,B2)) )
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
‹#›
Kapitel 9.4
Mengen-Operatoren
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Schnitt (1)
‹#›
Schnitt RS:

Lässt sich als Semi-Join über alle Attribute ausdrücken.

Im Grundsatz über die Algorithmen aus Kap. 9.4
umsetzbar.

Illustration am Beispiel GraceHash Join.
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Schnitt (2)
‹#›
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R
3
90
42
76
13
88
2
44
5
17
S
6
27
3
97
4
13
44
17
2
Mod 3
R
2
3
44
5
76
90
13
17
42
88
Mod 3
Partitionierung
S
44
17
97
4
6
27
2
13
3
DBI 9
Schnitt (3)
‹#›
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R
3
90
42
76
13
88
2
44
5
17
S
6
27
3
97
4
13
44
17
2
Mod 3
R
2
3
44
5
76
90
13
17
42
88
Mod 3
Build/Probe
S
44
17
97
4
6
27
2
13
3
DBI 9
Schnitt (4)
Build-Phase
R
3
90
42
76
13
88
2
44
5
17
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
Mod 5
6
27
3
‹#›
Hashtabelle
S
6
27
3
97
4
13
44
17
2
DBI 9
Schnitt (5)
R
3
90
42
76
13
88
2
44
5
17
R  S = {3 , }
Mod 5
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
6
27
3
‹#›
Probe-Phase
S
6
27
3
97
4
13
44
17
2
DBI 9
Schnitt (6)
R
3
90
42
76
13
88
2
44
5
17
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R  S = {3, }
97
13
4
‹#›
Build-Phase
Mod 5
S
6
27
3
97
4
13
44
17
2
DBI 9
Schnitt (7)
R  S = {3, 13 }
‹#›
Probe-Phase
R
3
90
42
76
13
88
2
44
5
17
Mod 5
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
97
13
4
S
6
27
3
97
4
13
44
17
2
DBI 9
Schnitt (8)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
R
3
90
42
76
13
88
2
44
5
17
S
6
27
3
97
4
13
44
17
2
Mod 3
Mod 3
R
2
3
44
5
76
90
13
17
42
88
R  S = {3, 13, 2, 44, 17 }
‹#›
S
44
17
97
4
6
27
2
13
3
DBI 9
Vereinigung (1)
‹#›
Mischen:
 Schritt 1: Sortierungen unter Duplikateliminierung
R und S müssen sortiert werden


Schritt 2: Mischen


Entfällt falls R und S bereits sortiert
In jeder Iteration: Falls aktuelle Tupel gleich, wird nur eines
übernommen, aber die Position beider fortgeschaltet.
Andernfalls wird das kleinere übernommen und dessen
Position fortgeschaltet.
Ergebnis sortiert.
C = B(R) + B(S) + ((1-f)(T(R)+T(S))/BfRS )
+ B(R)logm-1(B(R)/m) + B(S)logm-1(B(S)/m)
(f: Anteil an Duplikaten)
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
DBI 9
Vereinigung (2)
‹#›
Einfügen: Unsortiertes Ergebnis ohne Duplikate
R
Schon
gesehen?
Schritt 1
Schritt 2
S
nein
m-1 Puffer
Ausgabe-Puffer
Wichtig für die Performanz:
 RS sollte in den Puffer passen, daher nur
für beschränkte Kardinalität brauchbar.
Falls
S duplikatfrei, nur R im Puffer.
 Schnelle
© 2009 Univ,Karlsruhe, IPD, Prof. Lockemann
Zugriffsstruktur, z.B. Hashing.
DBI 9