Kapitel 8 Anfragebearbeitung Logische Optimierung Physische Optimierung Kostenmodelle ...

157
Kapitel 8 Anfragebearbeitung Logische Optimierung Physische Optimierung Kostenmodelle „Tuning“

Transcript of Kapitel 8 Anfragebearbeitung Logische Optimierung Physische Optimierung Kostenmodelle ...

Page 1: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Kapitel 8Anfragebearbeitung

Logische OptimierungPhysische OptimierungKostenmodelle„Tuning“

Page 2: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

2

Ablauf der Anfrageoptimierung

ScannerParser

Sichtenauflösung

Anfrage-Optimierer

CodeerzeugungAusführung

DeklarativeAnfrage

AlgebraischerAusdruck

Auswertungs-Plan (QEP)

Page 3: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

3

Kanonische Übersetzung

select A1, ..., Anfrom R1, ..., Rkwhere P

R1 R2

R3

Rk

P

A1, ..., An

Page 4: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

4

Kanonische Übersetzungselect Titelfrom Professoren, Vorlesungenwhere Name = ´Popper´ and PersNr = gelesenVon

Professoren Vorlesungen

Name = ´Popper´ and PersNr=gelesenVon

Titel

Titel (Name = ´Popper´ and PersNr=gelesenVon (Professoren Vorlesungen))

Page 5: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

5

Erste Optimierungsideeselect Titel from Professoren, Vorlesungenwhere Name = ´Popper´ and PersNr = gelesenVon

Professoren

Vorlesungen

PersNr=gelesenVon

Titel

Titel (PersNr=gelesenVon ((Name = ´Popper´ Professoren) Vorlesungen))

Name = ´Popper´

Page 6: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

6

Grundsätze: Sehr hohes Abstraktionsniveau der

mengenorientierten Schnittstelle (SQL). Sie ist deklarativ, nicht-prozedural, d.h. es wird

spezifiziert, was man finden möchte, aber nicht wie.

Das wie bestimmt sich aus der Abbildung der mengenorientierten Operatoren auf Schnittstellen-Operatoren der internen Ebene (Zugriff auf Datensätze in Dateien, Einfügen/Entfernen interner Datensätze, Modifizieren interner Datensätze).

Zu einem was kann es zahlreiche wie‘s geben: effiziente Anfrageauswertung durch Anfrageoptimierung.

i.Allg. wird aber nicht die optimale Auswertungsstrategie gesucht (bzw. gefunden) sondern eine einigermaßen effiziente VarianteZiel: „avoiding the worst case“

Optimierung von Datenbank- Anfragen

Page 7: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

7

1. Aufbrechen von Konjunktionen im Selektionsprädikat c1c2 ... cn (R ) c1(c2 (…(cn(R )) …))2. ist kommutativ c1(c2 ((R )) c2 (c1((R )) 3. -Kaskaden: Falls L1 L2 … Ln, dann gilt

L1( L2 (…( Ln(R )) …)) L1 (R )4. Vertauschen von und

Falls die Selektion sich nur auf die Attribute A1, …, An der Projektionsliste bezieht, können die beiden Operationen vertauscht werden

A1, …, An (c(R )) c (A1, …, An(R ))5. X, , und A sind kommutativ

R Ac S S Ac R

Äquivalenzerhaltende Transformationsregeln

Page 8: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

8

6. Vertauschen von mit A Falls das Selektionsprädikat c nur auf Attribute der

Relation R zugreift, kann man die beiden Operationen vertauschen:

c(R Aj S) c(R) Aj S

Falls das Selektionsprädikat c eine Konjunktion der Form „c1 c2“ ist und c1 sich nur auf Attribute aus R und c2 sich nur auf Attribute aus S bezieht, gilt folgende Äquivalenz:

c(R A j S) c(R) A j (c2 (S))

Äquivalenzerhaltende Transformationsregeln

Page 9: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

9

7. Vertauschung von mit ADie Projektionsliste L sei: L = {A1,…,An, B1,…,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 A c S) (A1, …, An (R)) A c (B1, …, Bn (S))Falls das Joinprädikat sich auf weitere Attribute, sagen wir A1', …, Ap', aus R und B1', …, Bq' aus S bezieht, müssen diese für die Join-Operation erhalten bleiben und können erst danach herausprojiziert werden:L (R A c S) L (A1, …, An, A1‘, …, An ‘ (R)

A c B1, …, Bn, B1‘, …, Bn ‘ (R)) Für die X-Operation gibt es kein Prädikat, so dass die Einschränkung entfällt.

Äquivalenzerhaltende Transformationsregeln

Page 10: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

10

8. Die Operationen A, X, , sind jeweils (einzeln betrachtet) assoziativ. Wenn also eine dieser Operationen bezeichnet, so gilt:

(R S ) T R (S T )9. Die Operation ist distributiv mit , , . Falls

eine dieser Operationen bezeichnet, gilt: c(R S) (c (R)) (c (S))10. Die Operation ist distributiv mit .

c(R S) (c (R)) (c (S))

Äquivalenzerhaltende Transformationsregeln

Page 11: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

11

11. Die Join- und/oder Selektionsprädikate können mittels de Morgan's Regeln umgeformt werden:

(c1 c2) (c1) (c2) (c1 c2) (c1) (c2)

12. 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 X S ) R Ac S

Äquivalenzerhaltende Transformationsregeln

Page 12: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

12

1. Mittels Regel 1 werden konjunktive Selektionsprädikate in Kaskaden von -Operationen zerlegt.

2. Mittels Regeln 2, 4, 6, und 9 werden Selektionsoperationen soweit „nach unten“ propagiert wie möglich.

3. Mittels Regel 8 werden die Blattknoten so vertauscht, dass derjenige, der das kleinste Zwischenergebnis liefert, zuerst ausgewertet wird.

4. Forme eine X-Operation, die von einer -Operation gefolgt wird, wenn möglich in eine A-Operation um

5. Mittels Regeln 3, 4, 7, und 10 werden Projektionen soweit wie möglich nach unten propagiert.

6. Versuche Operationsfolgen zusammenzufassen, wenn sie in einem „Durchlauf“ ausführbar sind (z.B. Anwendung von Regel 1, Regel 3, aber auch Zusammenfassung aufeinanderfolgender Selektionen und Projektionen zu einer „Filter“-Operation).

Heuristische Anwendung der Transformationsregeln

Page 13: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

13

Anwendung der Transformationsregelnselect distinct s.Semesterfrom Studenten s, hören h Vorlesungen v, Professoren pwhere p.Name = ´Sokrates´ and v.gelesenVon = p.PersNr and v.VorlNr = h.VorlNr and h.MatrNr = s.MatrNr

s h

v

p

p.Name = ´Sokrates´ and ...

s.Semester

Page 14: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

14

Aufspalten der Selektionsprädikate

s h

v

p

p.Name = ´Sokrates´ and ...

s.Semester

s hv

p

p.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

s.MatrNr=h.MatrNr

v.VorlNr=h.VorlNr

Page 15: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

15

Verschieben der Selektionsprädikate„Pushing Selections“

s h

vp

p.PersNr=v.gelesenVon

s.Semester

p.Name = `Sokrates`

s.MatrNr=h.MatrNr

v.VorlNr=h.VorlNr

s hv

p

p.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

s.MatrNr=h.MatrNr

v.VorlNr=h.VorlNr

Page 16: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

16

Zusammenfassung von Selektionen und Kreuzprodukten zu Joins

s h

vp

p.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

s.MatrNr=h.MatrNr

v.VorlNr=h.VorlNr

s h

vpAs.MatrNr=h.MatrNr

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

Page 17: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

17

Optimierung der JoinreihenfolgeKommutativität und Assoziativität ausnutzen

s

h

v

p

As.MatrNr=h.MatrNr

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

s h

vp

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

As.MatrNr=h.MatrNr

Page 18: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

18

Was hat´s gebracht?

s

h

v

p

As.MatrNr=h.MatrNr

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

s h

vp

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

As.MatrNr=h.MatrNr

13

13

4

1

3

4

4

Page 19: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

19

Einfügen von Projektionen

s

h

v

p

As.MatrNr=h.MatrNr

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

s

h

v

p

As.MatrNr=h.MatrNr

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

h.MatrNr

Page 20: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Organisatorisches DB2 Zertifizierungskurs

IBMFast kostenlos

Für gute Studierende DB Praktikum

Vorbesprechung 27. Januar 2009, 14.00 Uhr in unserem Ted Codd Seminarraum MI 02.09.014

unter http://dbpra.in.tum.de/dbpra-aktuell.shtml Vorlesung am kommenden Freitag 23.1.09 (neuer Stoff)

In Hörsaal 1 (hier!) Vorlesung am 30.1.09 …

Findet als Zentralübung statt (kein neuer Stoff)

20

Page 21: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

21

Eine weitere Beispieloptimierung

Page 22: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

22

Airport Connection

to

from N

N

1

Code

TransfTime

FlightNo

DepTime

ArrivalTime1

City

Airline

Page 23: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

23

Page 24: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

24

Page 25: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

25

Page 26: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

26

Page 27: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

27

Page 28: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

28

Page 29: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

29

Page 30: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Entschachtelung / Unnestingselect s.Name, p.VorlNr from Studenten s , prüfen pwhere s.MatrNr = p.MatrNr and p.Note = (

select min(p2.Note)from prüfen p2where s.MatrNr=p2.MatrNr )

select s.Name, p.VorlNr from Studenten s , prüfen p ,

(select p2.MatrNr as ID, min(p2.Note) as beste from prüfen p2 group by p2.MatrNr) m

where s.MatrNr=p.MatrNr and m.ID=s.MatrNr and p.Note=m.beste 30

automatisch

Page 31: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Dependent Join (nested loop Semantik)

31

Page 32: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Einfache Entschachtelung

32

Page 33: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

33

Page 34: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Weitere Transformationsregeln

34

Page 35: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Beispiel

35

Page 36: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Entkoppelung rechter Seite von linker Seite: optional

36

Page 37: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

37

Pull-basierte Anfrageauswertung

opennext

ReturnErgebnis

Page 38: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

38

Pipelining vs. Pipeline-Breaker

R S

...

...

...

T

...

...

...

Page 39: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

39

Pipelining vs. Pipeline-Breaker

R S

...

...

...

T

...

...

...

Page 40: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

40

Pipeline-Breaker Unäre Operationen

sortDuplikatelimination (unique,distinct)Aggregatoperationen (min,max,sum,...)

Binäre OperationenMengendifferenz

Je nach Implementierung JoinUnion

Page 41: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

41

Page 42: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

42

J1 nested (inner-outer) loop„brute force“-Algorithmus

foreach r Rforeach s S

if s.B = r.A then Res := Res (r s)

Implementierung der Verbindung: Strategien

Page 43: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

43

Page 44: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

44

Block-Nested Loop Algorithmus

Implementierung der Verbindung: Strategien

m-k m-k m-k m-k m-kR

kS k k k k k

Page 45: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

45

Page 46: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

46

Page 47: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

47

J4 Hash-Join R und S werden mittels der gleichen Hashfunktion h

– angewendet auf R.A und S.B – auf (dieselben) Hash-Buckets abgebildet

Hash-Buckets sind i.Allg. auf Hintergrundspeicher (abhängig von der Größe der Relationen)

Zu verbindende Tupel befinden sich dann im selben Bucket

Wird (nach praktischen Tests) nur vom Merge-Join „geschlagen“, wenn die Relationen schon vorsortiert sind

Implementierung der Verbindung: Strategien

Page 48: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

48

Implementierung der Verbindung: Strategien

A r1 5 r2 7 r3 8 r4 5

R SB 5 s17 s210 s35 s4

r1 5

s15

r4 5

s45

10 s3

r2 7

s27

r3 8

h(A) h(B )

Bucket 3Bucket 2Bucket 1

Page 49: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

49

„Normaler“ blockierender Hash-Join mit Überlauf: Partitionieren

Send

R

Send

S

receive

P1

P2

P3

Partitionh(R.A)

P1

P2

P3

Partitionh(S.A)

receive

Page 50: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

50

„Normaler“ blockierender Hash-Join mit Überlauf: Build/Probe

Send

R

Send

S

P1

P2

P3

Partitionh(R.A)

P1

P2

P3

buildHashtabelle

probe

Lade Blöcke von P1

Page 51: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

51

Page 52: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

52

Page 53: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

53

Page 54: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

54

Page 55: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

55

Hybrid Hash-Join Fange so an, als wenn der Build-Input S 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 R Jedes Tupel aus R, dessen potentielle Join-Partner im

Hauptspeicher sind, wird sogleich verarbeitet

Hybrid Hash-Join ist dann besonders interessant, wenn der Build-Input knapp größer als der Hauptspeicher istKostensprung beim normalen Hash-Join

Wird oft auch Grace-Hash-Join genannt, weil er für die Datenbankmaschine Grace in Japan erfunden wurde

Page 56: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

56

Hybrid Hash-Join

R S

P1

P2

P3

Hashtabelle

Page 57: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

57

Hybrid Hash-Join

R S

P3

P1

P2

Hashtabelle

Page 58: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

58

Hybrid Hash-Join

R S

P2

P3

P1

Hashtabelle

Page 59: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

59

Hybrid Hash-Join

R

P2

P3

Partitionh(R.A) P2

P3

Hashtabelle

probe

Wenn r zur ersten Partition

gehört

Page 60: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

60

Parallele Anfragebearbeitung:Hash Join

A´ B´

scan scan

split split

Ai Bi

join

merge merge

A´´ B´´

scan scan

split split

Aj Bj

join

merge merge

Page 61: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

61

Paralleler Hash Join – im Detail1. An jeder Station werden mittels Hash-Funktion h1 die

jeweiligen Partitionen von A und B in A1,...,Ak und B1,...,Bk zerlegt h1 muss so gewählt werden, dass alle Ai´s aller

Stationen in den Hauptspeicher passen2. Für alle 1 <= i <= n: Berechne jetzt den Join von Ai

mit Bi wie folgta. Wende eine weitere Hash-Funktion h2 an, um Ai

auf die l Stationen zu verteilen Sende Tupel t an Station h2(t)

b. Eintreffende Ai-Tupel werden in die Hash-Tabelle an der jeweiligen Station eingefügt

c. Sobald alle Tupel aus Ai „verschickt“ sind, wird h2 auf Bi angewendet und Tupel t an Station h2(t) geschickt

d. Sobald ein Bi-Tupel eintrifft, werden in der Ai-Hashtabelle seine Joinpartner ermittelt.

Page 62: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

62

Mengendurchschnitt (~Join) mit einem Hash/Partitionierungs-Algorithmus

R23

445

769013174288

S44179746

272

133

R S

• Nested Loop: O(N2)• Sortieren: O(N log N)• Partitionieren und

Hashing

Page 63: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

63

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R23

445

769013174288

S44179746

272

133

R SR3

90427613882

445

17

Mod

3

Page 64: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

64

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R23

445

769013174288

S44179746

272

133

R SR3

90427613882

445

17

S6

273

974

1344172

Mod

3

Mod

3

Page 65: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

65

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R23

445

769013174288

S44179746

272

133

R SR3

90427613882

445

17

S6

273

974

1344172

Mod

3

Mod

3

Page 66: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

66

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R SR3

90427613882

445

17

S6

273

974

1344172

6273

Mod 5

Build-Phase

Hashtabelle

Page 67: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

67

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R S = {3, }R3

90427613882

445

17

S6

273

974

1344172

6273

Mod 5

Probe-Phase

Page 68: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

68

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R S = {3, }R3

90427613882

445

17

S6

273

974

1344172

97134

Mod 5

Build-Phase2. Partition

Page 69: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

69

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R S = {3, }R3

90427613882

445

17

S6

273

974

1344172

97134

Mod 5

Probe-Phase2. Partition

Page 70: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

70

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R S = {3, 13 }R3

90427613882

445

17

S6

273

974

1344172

97134

Mod 5

Probe-Phase2. Partition

Page 71: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

71

Mengendurchschnitt mit einem Hash/Partitionierungs-Algorithmus

R23

445

769013174288

S44179746

272

133

R3

90427613882

445

17

S6

273

974

1344172

Mod

3

Mod

3

R S = {3, 13, 2, 44, 17 }

Page 72: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

72

Vergleich: Sort/Merge-Join versus Hash-Join

R run run S

merge m

erge

R partition partition S

Page 73: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

73

Prallelausführung von Aggregat-Operationen Min: Min(R.A) = Min ( Min(R1.A), ... , Min(Rn.A) ) Max: analog Sum: Sum(R.A) = Sum ( Sum(R1.a), ..., Sum(Rn.A) ) Count: analog Avg: man muß die Summe und die Kardinalitäten der

Teilrelationen kennen; aber vorsicht bei Null-Werten! Avg(R.A) = Sum(R.A) / Count(R) gilt nur wenn A keine Nullwerte

enthält.

Page 74: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

74

111100

111100 False

drops

6 Bit(realistisch |R|*k Bits)

Join mit Hashfilter(Bloom-Filter)

R1

R2pa

rtiti o

nie r

en

S1

S2

parti

ti oni

eren

Page 75: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

75

..

..

..

..

..

..

Join mit Hashfilter(False Drop Abschätzung)

Wahrscheinlichkeit, dass ein bestimmtes Bit j gesetzt istW. dass ein bestimmtes rR das Bit setzt: 1/bW. dass kein rR das Bit setzt: (1-1/b)|R|

W. dass ein rR das Bit gesetzt hat: 1- (1-1/b)|R|

01..j..

b-1

Page 76: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

76

Illustration: Externes Sortieren

971735

27162

9913

Page 77: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

77

Illustration: Externes Sortieren

971735

27162

9913

Page 78: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

78

Illustration: Externes Sortieren

971735

27162

9913

97

173

Page 79: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

79

Illustration: Externes Sortieren

971735

27162

9913

3

1797

sort

Page 80: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

80

Illustration: Externes Sortieren

971735

27162

9913

33

1797

1797

sortrun

Page 81: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

81

Illustration: Externes Sortieren

971735

27162

9913

53

1797

2716

run

Page 82: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

82

Illustration: Externes Sortieren

971735

27162

9913

53

17975

1627

1627

sortrun

Page 83: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

83

Illustration: Externes Sortieren

971735

27162

9913

23

17975

1627

9913

run

Page 84: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

84

Illustration: Externes Sortieren

971735

27162

9913

23

17975

16272

1399

1399

sortrun

Page 85: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

85

Illustration: Externes Sortieren

33

17975

16272

1399

52

mergerun

Page 86: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

86

Illustration: Externes Sortieren

2

33

17975

16272

1399

52

mergerun

Page 87: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

87

Illustration: Externes Sortieren

23 3

317975

16272

1399

513

mergerun

Page 88: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

88

Illustration: Externes Sortieren

235

173

17975

16272

1399

513

mergerun

Page 89: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

89

Illustration: Externes Sortieren

235

173

17975

16272

1399

1613

mergerun

Page 90: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

90

Illustration: Externes Sortieren

235

13

173

17975

16272

1399

1613

run

Page 91: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

91

Externes Sortieren: Merge mittels Heap/Priority Queue

317975

16272

1399

mergerun

3

5 2

Page 92: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

92

Externes Sortieren: Merge mittels Heap/Priority Queue

317975

16272

1399

mergerun

2

5 3

Page 93: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

93

Externes Sortieren: Merge mittels Heap/Priority Queue

2 317975

16272

1399

run2

5 3

Page 94: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

94

Externes Sortieren: Merge mittels Heap/Priority Queue

2 317975

16272

1399

run13

5 3

Ganz wichtig: aus dem grünen Run nachladen

(also aus dem Run, aus dem das Objekt stammte)

Page 95: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

95

Externes Sortieren: Merge mittels Heap/Priority Queue

2 317975

16272

1399

run3

5 13

Page 96: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

96

Externes Sortieren: Merge mittels Heap/Priority Queue

23

317975

16272

1399

run3

5 13

Page 97: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

97

Externes Sortieren: Merge mittels Heap/Priority Queue

23

317975

16272

1399

run17

5 13

Page 98: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

98

Externes Sortieren: Merge mittels Heap/Priority Queue

23

317975

16272

1399

run5

17 13

Page 99: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

99

Mehrstufiges Mischen / Merge

m

m

Level 0

Level 1

Level 2

Page 100: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

100

Replacement Selection während der Run-Generierung

971735

27162

9913

Ersetze Array durch Einen Heap

Page 101: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

101

Replacement Selection während der Run-Generierung

971735

27162

9913

Heap

97

Page 102: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

102

Replacement Selection während der Run-Generierung

971735

27162

9913

Heap

1-97

1-17

Page 103: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

103

Replacement Selection während der Run-Generierung

971735

27162

9913

Heap

1-17

1-97

Page 104: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

104

Replacement Selection während der Run-Generierung

971735

27162

9913

Heap

1-17

1-97 1-3

Page 105: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

105

Replacement Selection während der Run-Generierung

971735

27162

9913

Heap

1-3

1-97 1-17

Page 106: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

106

Replacement Selection während der Run-Generierung

971735

27162

9913

3Heap

1-3

1-97 1-17

Page 107: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

107

Replacement Selection während der Run-Generierung

971735

27162

9913

3Heap

1-5

1-97 1-17

Page 108: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

108

Replacement Selection während der Run-Generierung

971735

27162

9913

35

Heap

1-5

1-97 1-17

Page 109: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

109

Replacement Selection während der Run-Generierung

971735

27162

9913

35

Heap

1-27

1-97 1-17

Page 110: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

110

Replacement Selection während der Run-Generierung

971735

27162

9913

35

Heap

1-27

1-97 1-17

Page 111: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

111

Replacement Selection während der Run-Generierung

971735

27162

9913

35

Heap

1-17

1-97 1-27

Page 112: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

112

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17

Heap

1-17

1-97 1-27

Page 113: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

113

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17

Heap

2-16

1-97 1-27

Nächster Run, kleiner

als 17

Page 114: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

114

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17

Heap

2-16

1-97 1-27

Nächster Run, kleiner

als 17

Page 115: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

115

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17

Heap

1-27

1-97 2-16

Page 116: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

116

Replacement Selection während der Run-Generierung

971735

27162

9913

35

1727

Heap

1-27

1-97 2-16

Page 117: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

117

Replacement Selection während der Run-Generierung

971735

27162

9913

35

1727

Heap

2-2

1-97 2-16

Page 118: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

118

Replacement Selection während der Run-Generierung

971735

27162

9913

35

1727

Heap

2-2

1-97 2-16

Page 119: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

119

Replacement Selection während der Run-Generierung

971735

27162

9913

35

172797

Heap

1-97

2-2 2-16

Page 120: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

120

Replacement Selection während der Run-Generierung

971735

27162

9913

35

172797

Heap

1-99

2-2 2-16

Page 121: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

121

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17279799

Heap

1-99

2-2 2-16

Page 122: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

122

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17279799

Heap

2-13

2-2 2-16

Page 123: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

123

Replacement Selection während der Run-Generierung

971735

27162

9913

35

17279799

Heap

2-2

2-13 2-16

Page 124: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

124

Replacement Selection während der Run-Generierung

971735

27162

9913

35

172797992

1316

Heap

2-2

2-13 2-16

Page 125: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

125

Implementierungs-Details Natürlich darf man nicht einzelne Datensätze zwischen

Hauptspeicher und Hintergrundspeicher transferieren Jeder „Round-Trip“ kostet viel Zeit (ca 10 ms)

Man transferiert größere BlöckeMindestens 8 KB Größe

Replacement Selection ist problematisch, wenn die zu sortierenden Datensätze variable Größe habeDer neue Datensatz passt dann nicht unbedingt in

den frei gewordenen Platz, d.h., man benötigt eine aufwendigere Freispeicherverwaltung

Replacement Selection führt im Durchschnitt zu einer Verdoppelung der Run-LängeBeweis findet man im [Knuth]

Komplexität des externen Sortierens? O(N log N) ??

Page 126: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

126

Algorithmen auf sehr großen Datenmengen

R23

445

789013174289

S44179756

272

139

R S

• Nested Loop: O(N2)• Sortieren: O(N log N)• Partitionieren und

Hashing

Page 127: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

127

Übersetzung der logischen Algebra

R S

AR.A=S.B

R S

HashJoinR.A=S.B

R S

MergeJoinR.A=S.B

[SortR.A] [SortS.B]

R

S

IndexJoinR.A=S.B

[HashS.B | TreeS.B]

R

S

NestedLoopR.A=S.B

[Bucket]

Page 128: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

128

Übersetzung der logischen Algebra

P

R

SelectP

R

IndexSelectP

R

Page 129: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

129

Übersetzung der logischen Algebra

l

R

[NestedDup]

Projectl

R

[SortDup]

Sort

Projectl

R

[IndexDup]

[Hash | Tree]

Projectl

R

Page 130: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

130

Ein AuswertungsplanEin Auswer-tungsplan

Page 131: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

131

Wiederholung der Optimierungsphasenselect distinct s.Semesterfrom Studenten s, hören h Vorlesungen v, Professoren pwhere p.Name = ´Sokrates´ and v.gelesenVon = p.PersNr and v.VorlNr = h.VorlNr and h.MatrNr = s.MatrNr

s h

v

p

p.Name = ´Sokrates´ and ...

s.Semester

Page 132: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

132

s

h

v

p

As.MatrNr=h.MatrNr

Ap.PersNr=v.gelesenVon

s.Semester

p.Name = ´Sokrates´

Av.VorlNr=h.VorlNr

Page 133: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

133

Kostenbasierte Optimierung Generiere alle denkbaren Anfrageausertungspläne

Enumeration Bewerte deren Kosten

KostenmodellStatistikenHistogrammeKalibrierung gemäß verwendetem RechnerAbhängig vom verfügbaren SpeicherAufwands-Kostenmodell

Durchsatz-maximierendNicht Antwortzeit-minimierend

Behalte den billigsten Plan

Page 134: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

134

ProblemgrößeSuchraum (Planstruktur)1. # Bushy-Pläne mit n Tabellen [Ganguly et al. 1992]:

n en (2(n-1))!/(n-1)!

2 7 25 146 1680

10 22026 1,76*1010

20 4,85 * 109 4,3*1027

(2(n-1))!(n-1)!

2. Plankosten unterscheiden sich um Größenordnungen3. Optimierungsproblem ist NP-hart [Ibaraki 1984]

Page 135: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

135

Page 136: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

136

Sind verschiedene Strategien anwendbar, so benötigt man zur Auswahl eine Kostenfunktion. Sie basiert auf dem Begriff der Selektivität.

Die Selektivität eines Suchprädikats schätzt die Anzahl der qualifizierenden Tupel relativ zur Gesamtanzahl der Tupel in der Relation.

Beispiele:die Selektivität einer Anfrage, die das

Schlüsselattribut einer Relation R spezifiziert, ist 1/ #R, wobei #R die Kardinalität der Relation R angibt.

Wenn ein Attribut A spezifiziert wird, für das i verschiedene Werte existieren, so kann die Selektivität als

(#R/i) / #R oder 1/iabgeschätzt werden.

Selektivität

Page 137: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

137

Page 138: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

138

Abschätzung für einfache Fälle

Page 139: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

139

Page 140: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

140

Parametrisierte Verteilung

Histogramm

Page 141: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

141

Page 142: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

142

I/O-Kosten: Block Nested Loop Join

Page 143: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

143

Tuning von Datenbanken Statistiken (Histogramme, etc.) müssen explizit

angelegt werden Anderenfalls liefern die Kostenmodelle falsche Werte In Oracle …

analyze table Professoren compute statistics for table;

Man kann sich auch auf approximative Statistiken verlassenAnstatt compute verwendet man estimate

In DB2 … runstats on table …

Page 144: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

144

Analysieren von Leistungsengpässen

Geschätzte Kosten von

Oracle

Page 145: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

145

Baumdarstellung

Page 146: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

146

BeispielAnfrage

SELECT *FROM A, B, CWHERE A.a = B.a AND

B.b = C.a ;

• Blätter Tabellen• innere Knoten Operatoren• Annotation Ausführungsorte

shipclient

IdxNLJ1

idxscan3

fscan2fscan1

A1

C3

B2

HashJ1

Auswertungsplan

Page 147: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

147

Algorithmen - Ansätze Erschöpfende Suche

Dynamische Programmierung (System R)A* Suche

Heuristiken (Planbewertung nötig)Minimum Selectivity, Intermediate Result,...KBZ-Algorithmus, AB-Algorithmus

Randomisierte Algorithmen Iterative ImprovementSimulated Annealing

Page 148: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

148

ProblemgrößeSuchraum (Planstruktur)1. # Bushy-Pläne mit n Tabellen [Ganguly et al. 1992]:

n en (2(n-1))!/(n-1)!

2 7 25 146 1680

10 22026 1,76*1010

20 4,85 * 109 4,3*1027

(2(n-1))!(n-1)!

2. Plankosten unterscheiden sich um Größenordnungen3. Optimierungsproblem ist NP-hart [Ibaraki 1984]

Page 149: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

149

Dynamische Programmierung II

Identifikation von 3 Phasen1. Access Root - Phase: Aufzählen der Zugriffspläne2. Join Root - Phase: Aufzählen der Join-Kombinationen3. Finish Root - Phase: sort, group-by, etc.

Page 150: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

150

Optimierung durch Dynamische Programmierung

Standardverfahren in heutigen relationalen Datenbanksystemen

Voraussetzung ist ein Kostenmodell als Zielfunktion

I/O-KostenCPU-Kosten

DP basiert auf dem Optimalitätskriterium von Bellman

Literatur zu DP: D. Kossmann und K. Stocker: Iterative Dynamic

Programming, TODS, 2000 to appear (online)

O S-O

OptimalerSubplan

OptimalerSubplan

Page 151: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

151

DP - Beispiel

Index Pläne{ABC}{BC}{AC}{AB}{C}{B}{A}

1. Phase: Zugriffspläne ermitteln

Page 152: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

152

DP - Beispiel

Index Pläne{ABC}{BC}{AC}{AB}{C} scan(C){B} scan(B), iscan(B){A} scan(A)

1. Phase: Zugriffspläne ermitteln

Page 153: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

153

DP - Beispiel

Index Pläne{ABC

}{BC} ...{AC} s(A) A s(C), s(C) A s(A){AB} s(A) A s(B), s(A) A is(B), is(B) A

s(A),... {C} scan(C){B} scan(B), iscan(B){A} scan(A)

Pruning2. Phase: Join-Pläne ermitteln (2-fach,...,n-fach)

Page 154: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

154

DP - Beispiel

Index

Pläne

{ABC} (is(B) A s(A)) A s(C)

{BC} ...{AC} s(A) A s(C){AB} s(A) A is(B), is(B) A s(A) {C} scan(C){B} scan(B), iscan(B){A} scan(A)

3. Phase: Finalisierung

Page 155: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

Algorithmus DynProg

155

Page 156: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

156

Page 157: Kapitel 8 Anfragebearbeitung  Logische Optimierung  Physische Optimierung  Kostenmodelle  „Tuning“

157

Enumeration Effiziente Enumeration [Vance 96]

anstatt zunächst alle 2-elem, 3-elem, ..., n-elem Pläne sequentiell zu enumerieren: effizientes Interleaving

nur Pläne aus bereits berechneten Zeilen notwendig Beispiel:

1. A 2. B 3. AB 4. C 5. AC 6. BC 7. ABC