chapter08.pdf

Anwendersoftware (AS) Anwendersoftware (AS)

Datenbanken und Informationssysteme

Kapitel 8: Implementierung Relationaler Operatoren

Bernhard Mitschang Universitt Stuttgart

Wintersemester 2013/2014

Teile zu diesem Folienskript beruhen auf einer hnlichen Vorlesung, gehalten von Prof. Dr. T. Hrder am Fachbereich Informatik der Universitt Kaiserslautern und Prof. Dr. N. Ritter am Fachbereich Informatik der Universitt Hamburg. Fr dieses Skriptum verbleiben alle Rechte (insbesondere fr Nachdruck) bei den Autoren.

Tabellenoperationen, Implementierung

2

bersicht

Ziele Planoperatoren auf einer Tabelle Planoperatoren auf mehreren Tabellen Verbundalgorithmen

Nested-Loop-Verbund Sort-Merge-Verbund Hash-Verbund (classic hashing, simple hash join, hybrid hash join) Nutzung von typbergreifenden Zugriffspfaden verteilte Verbundalgorithmen

weitere binre Operationen (Mengenoperationen)

Quelle: [Mit95]


3

Ziele

Ziele Systematische Entwicklung von relationalen Verarbeitungskonzepten fr eine

oder mehrere Tabellen Realisierung von Planoperatoren

Operationen der Relationenalgebra unre Operationen: , binre Operationen: , , , , ,

Planoperatoren SQL-Anfragen enthalten logische Ausdrcke, die auf die Operationen der

Relationenalgebra zurckgefhrt werden knnen. Sie werden in Zugriffsplne umgesetzt. So genannte Planoperatoren implementieren diese logischen Operationen.


4

Planoperatoren

Planoperator: Implementierung der in einer Anfrage enthaltenen logischen Operatoren. Die in einer SQL-Anfrage enthaltenen logischen Ausdrcke, lassen sich z.B. auf die Operationen der Relationenalgebra zurckfhren.

bersicht:

Planoperatoren

Selektion Projektion Verbund Sortierung

Tabellen-Scan

Index-Scan

TID-Algorithmus Nested-Loop

Sort-Merge

Hash

Modifikation


5

Planoperatoren fr Selektion und Projektion

Allgemeine Auswertungsmglichkeiten fr die Selektion: direkter Zugriff ber

- eine gegebene TID - ein Hash-Verfahren - eine ein- bzw. mehrdimensionale Indexstruktur

sequentielle Suche in einer Tabelle Suche ber eine Indexstruktur (Indextabelle, Bitliste) Auswahl mit Hilfe mehrerer Verweislisten, wobei mehr als eine

Indexstruktur ausgenutzt werden kann Suche ber eine mehrdimensionale Indexstruktur

Projektion wird typischerweise in Kombination mit Sortierung, Selektion

oder Verbund durchgefhrt


6

Planoperatoren fr die Selektion

Nutzung des Scan-Operators Definition von Start- und Stopp-

Bedingung Definition von einfachen

Suchargumenten (Simple Search Argument)

Definition der Suchrichtung: NEXT/PRIOR, FIRST/LAST, n-th

Weitere Planoperatoren in Kombination mit der Selektion Sortierung Gruppenbildung (siehe

Sortieroperator) spezielle Operatoren z.B. in Data-

Warehouse-Anwendungen zur Gruppen- und Aggregatbildung (CUBE-Operator)


7

Planoperatoren fr die Selektion

Tabellen-Scan (Relationen-Scan) immer mglich SCAN-Operator implementiert die

Selektionsoperation

Index-Scan Auswahl des kostengnstigsten Index Spezifikation des Suchbereichs (Start-,

Stopp-Bedingung)

k-d-Scan Auswertung mehrdimensionaler

Suchkriterien Nutzung verschiedener

Auswertungsrichtungen durch Navigation

TID-Algorithmus Auswertung aller brauchbaren"

Indexstrukturen Auffinden von variabel langen TID-Listen Boolesche Verknpfung der einzelnen

Listen Zugriff zu den Tupeln entsprechend der

Trefferliste

I

I1 I2

TID-Listen

Tabelle


8

Planoperatoren fr Modifikationen

nderungen sind in SQL mengenorientiert, aber auf eine Tabelle beschrnkt INSERT, DELETE und UPDATE werden direkt auf die entsprechenden Operationen

der Speicherungsstrukturen abgebildet automatische Abwicklung von Wartungsoperationen zur Aktualisierung von

Zugriffspfaden, zur Gewhrleistung von Cluster-Bildung und Reorganisation usw. Durchfhrung von Logging- und Recovery-Manahmen usw.


9

Planoperatoren auf mehreren Tabellen

SQL erlaubt komplexe Anfragen ber k Tabellen Ein-Variablen-Ausdrcke:

beschreiben Bedingungen fr die Auswahl von Elementen aus einer Tabelle. Zwei-Variablen-Ausdrcke:

beschreiben Bedingungen fr die Kombination von Elementen aus zwei Tabellen.

k-Variablen-Ausdrcke: werden typischerweise in Ein- und Zwei-Variablen-Ausdrcke zerlegt und durch entsprechende Planoperatoren ausgewertet


10


Allgemeine Auswertungsmglichkeiten: Schleifeniteration (nested iteration)

fr jedes Element der ueren Tabelle Ra Durchlauf der inneren Tabelle Ri

- O(Na Ni + Na) - wichtigste Anwendung: nested loops join

Mischmethode (merge method) sequentieller, schritthaltender Durchlauf beider Tabellen R1, R2

- O(N1 + N2) - ggf. zustzliche Sortierkosten - wichtigste Anwendung: merging join

Hash-Methode (hashing) Partitionierung der inneren Tabelle Ri. Laden der p Partitionen in eine Hash-Tabelle HT im HSP. Probing der ueren Tabelle Ra oder ihrer entsprechenden Partitionen mit HT:

- O(p Na + Ni)


11


n-Wege-Verbunde Zerlegung in n-1 Zwei-Wege-Verbunde Wahl der Verbundreihenfolge

- maximal n! verschiedene Reihenfolgen - Anzahl der Verbundreihenfolgen ist abhngig von den gewhlten Verbundattributen - Optimale Reihenfolge abhngig von

Gre der Operanden Planoperatoren passende Sortierordnungen fr Verbundattribute Einsatz von Pipelining-Techniken, etc.

- Heuristik: Gre der Zwischenergebnisse minimieren

Wahl des Planoperators fr die einzelnen Zwei-Wege-Verbunde - Kosten der Operation abhngig von

Gre der Operanden passende Sortierordnungen, etc.

Mengenoperationen analoge Vorgehensweise


12


Einige Verbundreihenfolgen mit Zwei-Wege-Verbunden (n=5)

R4

R3

R2 R1

R5

Ergebnis

R3 R2 R1

R5

Ergebnis

R4

R4

R5

R3 R1

R2

Ergebnis

left-deep bushy right-deep


13

bersicht





14

Planoperatoren fr den Verbund

Verbund satztypbergreifende Operation: gewhnlich sehr teuer hufige Nutzung: wichtiger Optimierungskandidat typische Anwendung: Gleichverbund allgemeiner -Verbund selten

Implementierung der Verbundoperation kann gleichzeitig Selektionen auf den beteiligten Tabellen R und S ausfhren VA: Verbundattribute PR und PS: Prdikate definiert auf Selektionsattributen (SA) von R und S

SELECT * FROM R, S WHERE R.VA S.VA AND PR AND PS


15

Planoperatoren fr den Verbund

Mgliche Zugriffspfade Scans ber R und S (immer) Scans ber IR(VA), IS(VA) (wenn vorhanden)

liefern Sortierreihenfolge nach VA Scans ber IR(SA), IS(SA) (wenn vorhanden)

ggf. schnelle Selektion fr PR und PS Scans ber andere Indexstrukturen (wenn vorhanden)

ggf. schnelleres Auffinden aller Stze

VA: Verbundattribute PR und PS: Prdikate definiert auf Selektionsattributen (SA) von R und S


16

Nested-Loop-Verbund

Annahmen: Stze in R und S sind nicht nach den Verbundattributen geordnet es sind keine Indexstrukturen IR(VA) und IS(VA) vorhanden

Algorithmus fr -Verbund: Komplexitt: O(N2) fr N = NR = NS Inakzeptables Leistungsverhalten wenn die Tupel der inneren Relation R nicht

vollstndig in den Hauptspeicher passen.

Scan ber S, fr jeden Satz s, falls PS: Scan ber R, fr jeden Satz r, falls PR AND (r.VA s.VA): fhre Verbund aus, d. h., bernehme kombinierten Satz (r, s) in die Ergebnismenge.

Quelle: [Gra93]


17

Nested-Loop-Verbund

Nested-Loop-Verbund mit Indexzugriff

Nested-Block-Verbund

Scan ber S, fr jeden Satz s, falls PS: ermittle mittels Zugriff auf IR(VA) alle TIDs fr Stze mit r.VA = s.VA, fr jedes TID: hole Satz r, falls PR: bernehme kombinierten Satz (r, s) in die Ergebnismenge.

Scan ber S, fr jede Seite (bzw. Menge aufeinander folgender Seiten) von S: Scan ber R, fr jede Seite (bzw. Menge aufeinander folgender Seiten) von R: fr jeden Satz s der S-Seite, falls PS: fr jeden Satz r der R-Seite, falls PR AND (r.VA s.VA): bernehme kombinierten Satz (r, s) in die Ergebnismenge.


18

Sort-Merge-Verbund

Algorithmus besteht aus 2 Phasen: Phase 1: Sortierung von R und S nach R(VA) und S(VA)

(falls nicht bereits vorhanden), dabei frhzeitige Eliminierung nicht bentigter Stze (PR, PS)

Phase 2: schritthaltende Scans ber sortierte R- und S-Stze mit Durchfhrung des Verbundes bei r.VA = s.VA

Komplexitt: O(N log N) fr N = NR = NS Spezialfall:

Falls IR(VA) und IS(VA) oder verallgemeinerte Zugriffspfadstruktur ber R(VA) und S(VA) (Join-Index) vorhanden

Ausnutzung von Indexstrukturen auf Verbundattributen:

Schritthaltende Scans ber IR(VA) und IS(VA): fr jeweils zwei Schlssel aus IR(VA) und IS(VA), falls r.VA = s.VA: hole mit den zugehrigen TIDs die Tupel, falls PR und PS: bernehme kombinierten Satz (r, s) in die Ergebnismenge.


19

Hash-Verbund

Einfachster Fall (classic hashing): Schritt 1: Abschnittsweises Lesen der (kleineren) Tabelle R und Aufbau einer

Hash-Tabelle mit hA(r(VA)) nach Werten von R(VA) entsprechend den Abschnitten Ri (1 i p), so dass jeder der p Abschnitte in den verfgbaren Hauptspeicher passt und jeder Satz PR erfllt.

Schritt 2: berprfung (Probing) fr jeden Satz von S der PS erfllt. Im Erfolgsfall Durchfhrung des Verbunds.

Schritt 3: Wiederhole Schritt 1 und 2 solange, bis R erschpft ist.


20

Hash-Verbund

Aufbau der Hash-Tabelle und Probing Es erfolgt ein Scan ber R Dabei wird die Hash-Tabelle Hi (1 i p) der Reihe nach im HSP aufgebaut

Komplexitt: O(p NS) Spezialfall:

R passt in den Hauptspeicher: eine Partition (p = 1) ein Scan ber S gengt

Hp

H1

Scan ber S mit Probing von H1

Scan ber S mit Probing von Hp

R

S

R

S


21

Hash-Verbund

Partitionieren von R mit hp(r(VA)) Warum ist diese Partitionierung eine

kritische Operation? Welche Hilfsoperationen knnen

erforderlich sein? Ist fr die Partitionierung der Einsatz

einer Hash-Funktion notwendig?

Ableitung der Hi Partitionierung von R in Teilmengen

R1, R2, ..., Rp Ein Satz r von R ist in Ri, wenn

hp(r(VA)) in Hi ist.

Tabelle S wird mit derselben Funktion hp unter Auswertung von PS partitioniert

Varianten des Hash-Verbundes Unterschiede vor allem in der Art der

Partitionsbildung

0 100

#Tupel/ VA-Wert

VA

0 1

#Tupel/ VA-Wert

VA' 0.33 0.66

hP(r(VA))

R1 R2 R3

R

H1 H2 Hp

hp


22

Simple Hash Join

Schritt 1: Fhre Scan auf kleinerer Tabelle R aus berprfe PR und wende auf qualifizierte

Tupel r die Hash-Funktion hp an Fllt hp(r(VA)) in den gewhlten Bereich,

trage r in H1 ein Anderenfalls schreibe r in einen Puffer fr

die Ausgabe in eine Datei Rrest fr bergangene r-Tupel

Schritt 2: Fhre Scan auf S aus berprfe PS und wende auf qualifizierte

Tupel s die Hash-Funktion hp an Fllt hp(s(VA)) in den gewhlten Bereich,

suche in H1 einen Verbundpartner (Probing)

Falls erfolgreich, bilde ein Verbundtupel und ordne es dem Ergebnis zu

Anderenfalls schreibe s in einen Puffer fr die Ausgabe in eine Datei Srest fr bergangene s-Tupel

Schritt 3: Wiederhole Schritt 1 und 2 mit den bisher

bergangenen Tupeln auf Hi solange, bis Rrest erschpft ist

Dabei ist die berprfung von PR und PS nicht mehr erforderlich

Partitionierungstechnik beim einfachen Hash-Verbund gezeigt an Aufbau und Probing von H1:

R

H1 Rrest Srest

S

1. Iteration

Schritt 1

Schritt 2


23

Grace Join

Partitionsbildung findet vor dem Verbund statt Partitionen Ri und Si sind in Dateien zwischengespeichert Aufbau von Hi im HSP mit Ri und Probing mit Si

. . .

R1 H1

HP

S1 Scan ber S1 mit Probing von H1

Rp Sp

Scan ber SP mit Probing von HP


24

Hybrid Hash Join

Optimiert das Verfahren dadurch, dass parallel zur Partitionsbildung Aufbau und Probing von H1 erfolgt

HSP-Bereich jeweils 1 Seite

R2 H2

S2 Scan

2)

3) . . .

wie bei Grace-Verbund

R 1) Scan

a) HSP . . . R1 in H1 aufgebaut

R2 R3 Rp

b) S

Scan

S2 S3 Sp sofortiges

Probing von S1-Tupel


25

Nutzung typbergreifender Zugriffspfade

Verbund ber Link-Strukturen Ausnutzung hierarchischer

Zugriffspfade fr den Gleichverbund

Weitere Verfahren Verbundindexe, die fr bestimmte

-Verbunde eingerichtet sind

Nutzung von verallgemeinerten Zugriffspfadstrukturen

R S

TID r2

TID r1

TID r2

TID r2

TID s4

TID s3

TID s2

TID s6

logische Sicht

VIR

R S

TID r1

TID r2

TID r2

TID r2

TID s3

TID s2

TID s4

TID s6

Index auf TIDR

VIS

S R

TID s2

TID s3

TID s4

TID s6

TID r2

TID r1

TID r2

TID r2

Index auf TIDS

Scan ber R (Owner-Tabelle), fr jeden Satz r, falls PR: Scan ber zugehrige Link-Struktur LR-S(VA), fr jeden Satz s, falls PS: bernehme kombinierten Satz (r, s) in die Ergebnismenge

K58 K78 K88

K53

K25 K36 K47

1 3 1 4 Z 11 K55

Zeigerlisten fr

A1

Zeigerlisten fr A2 und A4

Optionaler Zeiger zur berlaufseite

A3

Z 21 Z 22 Z 23 Z 31 Z 41 Z 42 Z 43 Z 44


26

Verbundalgorithmen Vergleich

Nested-Loop-Verbund ist immer anwendbar, jedoch ist dabei stets das vollstndige Durchsuchen des gesamten Suchraums in Kauf zu nehmen.

Merge-Verbund bentigt die geringsten Suchkosten, verlangt aber, dass die Eingabestrme bereits sortiert sind. Indexstrukturen auf beiden Verbundattributen erfllen diese Voraussetzung. Sonst reduziert das Sortieren beider Tabellen nach den Verbundattributen den Kostenvorteil in erheblichem Mae. Ein Sort-Merge-Verbund kann dennoch zustzliche Vorteile besitzen, falls das Ergebnis in sortierter Folge verlangt wird und das Sortieren des groen Ergebnisses aufwendiger ist als das Sortieren zweier kleiner Ergebnismengen.

Beim Hash-Verbund wird der Suchraum partitioniert. In Bild c ist unterstellt, dass die gleiche Hash-Funktion h auf die Tabellen R und S angewendet worden ist. Die Partitionsgre (bei der kleineren) Tabelle richtet sich nach der verfgbaren Puffergre im Hauptspeicher. Eine Verkleinerung der Partitionsgre, um den Fall b anzunhern, verursacht hhere Vorbereitungskosten und ist deshalb nicht zu empfehlen.

Elementvergleich

(a) Nested-Loop-Verbund (b) Merge-Verbund

Eingabestrom 2

Eing

abes

trom

1

e 11 e 12 e 13

e 21 e 22 e 23 ...

Hash-Partitionen

(c) Hash-Verbund

Elementvergleich, der zu einem Verbundelement fhrt


Verbundalgorithmen in verteilten DBS

Problemstellung: Anfrage in Knoten K, die einen Verbund zwischen (Teil-)Tabellen R

am Knoten KR und (Teil-)Tabelle S am Knoten KS erfordert Festlegung des Ausfhrungsknotens: K, KR oder KS

Bestimmung der Auswertestrategie Sende beteiligte Tabellen vollstndig an einen Knoten und fhre

lokale Verbundberechnung durch (ship whole) - minimale Nachrichtenanzahl - sehr hohes bertragungsvolumen

Fordere fr jeden Verbundwert der ersten Tabelle zugehrige Tupel der zweiten an (fetch as needed)

- hohe Nachrichtenanzahl - nur relevante Tupel werden bercksichtigt

27


Verbundalgorithmen in verteilten DBS

Kompromisslsungen: Mit Semi-Verbund bzw. Erweiterungen wie Bit-Vektor-Verbund (hash filter join)

Semi-Verbund Versenden einer Liste der VA von R

( R' = VA(R) ) zum Knoten S Ermitteln der Verbundpartner in S

( S' = S VAR' = S(S VAR') ) Zurckschicken zum Knoten von R Durchfhrung des Verbunds

( R S' )

Bit-Vektor-Verbund hnlich wie Semi-Verbund, nur Versenden eines durch Hash-Funktion erstellten

Bitvektors (Bloom-Filter) Rcksenden einer Obermenge der Verbundpartner in S

28


29

ABTEILUNGEN

ANGESTELLTE Mnchen

Frankfurt

Verschicke die ganze VA-Spalte

Finde Verbund- partner

Verschicke projizierte Verbundpartner zurck

Verbund

Semi-Verbund

ABTNR ORT MGR

47

39

64

ABTNR NAME

47 Hans

47 Anna

ABTNR

47

39

64

ABTNR NAME ADRESSE TEL

69

28

75

47 Hans

47 Anna


30

Bit-Vektor-Verbund

1 0 0 0 0 0 1 1

ABTEILUNGEN

ANGESTELLTE 1 0 0 0 0 0 1 1

Erzeuge Bit-Vektor durch Hashing

Verschicke Bit-Vektor

Hashing der ABTNR-Werte, um potentielle Verbundkandidaten zu finden

Verschicke die potentiellen Verbundkandidaten zurck

Test + Verbund

ABTNR ORT MGR

47

39

64


28

47 Hans

47 Anna


69

28

75

47 Hans

47 Anna

Mnchen

Frankfurt


31

bersicht





32

Welche Mengenoperationen werden bentigt?

Welche Algorithmen lassen sich fr diese Mengenoperationen heranziehen? Was muss jeweils verglichen werden? Wie lsst sich eine Verbindung zu den Verbundalgorithmen herstellen?

Mengenoperationen

Operations-ergebnis

bereinstimmung in allen Attributen

bereinstimmung in einem oder mehreren Attributen

A Differenz (R-S) Anti-Semiverbund

B Durchschnitt Verbund, Semiverbund

C Differenz (S-R) Anti-Semiverbund

A, B Linksseitiger uerer Verbund

A, C Anti-Differenz Anti-Verbund

B, C Rechtsseitiger uerer Verbund

A, B, C Vereinigung Symmetrischer uerer Verbund

R, S vereinigungsvertrgliche Eingabestrme A, B, C Elementmengen

R

A B C

S

Quelle: [Gra93]


33

Binre Matching-Operationen

Mengen- und Verbundoperationen sind eng miteinander verwandt! erledigen grundstzlich dieselbe Aufgabe:

binary one-to-one matching operations ein Eingabe-Element trgt zur Ausgabe abhngig von seinem Match

mit einem anderen Eingabe-Element bei Operationen erfordern immer wieder die gleichen Schritte und knnen

deshalb mit denselben Algorithmen implementiert werden

Gleiche logische Vorgehensweise aus R und S werden drei Elementmengen gebildet: A, B, C Elemente in B passen zueinander! Wie knnen diese drei Elementmengen gebildet werden?

- mit Schleifeniteration - mit Mischmethode - mit Hash-Methode


34

Binre Matching-Operationen

Vereinheitlichtes Realisierungskonzept Vergleich von Verbund- vs. Primrschlssel-Attributen Gemeinsamkeit: Stze werden auf der Basis von Attributwerten gruppiert Dabei sind einige unre Operationen mit speziellen Manahmen mglich

- Gruppierung und Sortierung erlaubt einfache Duplikateliminierung - Bei Aggregation wird ein Attributwert pro Gruppe bestimmt - Beim Verbund ist die Gruppierung der potentiellen Verbundpartner kosteneffektiv

(entweder in Partitionen oder einer Sortierordnung) - Bei Mengenoperationen knnen die Elementmengen A, B, C gefunden werden;

dabei wird Duplikateliminierung mglich


35

Zusammenfassung

Selektionsoperationen vorhandene Zugriffspfadtypen erfordern zugeschnittene Operationen und effiziente

Abbildung Kombination verschiedener Zugriffspfade mglich (TID-Algorithmus)

Allgemeine Klassen von Auswertungsverfahren fr binre Operationen Schleifeniteration (nested iteration) Mischmethode (merge method) Hash-Methode (hashing)

Viele Optionen zur Durchfhrung von Verbundoperationen Nested-Loop-Verbund Sort-Merge-Verbund Hash-Verbund und Variationen

Mengenoperationen prinzipiell Nutzung der gleichen Verfahrensklassen Variation der Vergleichsdurchfhrung


36

Literatur zu diesem Kapitel

[Gra93] Goetz Graefe: Query Evaluation Techniques for Large Databases. ACM Comput. Surv. Vol. 25, No. 2, 1993.

[Mit95] Mitschang, B.: Anfrageverarbeitung in Datenbanksystemen - Entwurfs- und Implementierungskonzepte, Reihe Datenbanksysteme, Vieweg-Verlag, 1995.

Datenbanken und InformationssystemebersichtZielePlanoperatorenPlanoperatoren fr Selektion und ProjektionPlanoperatoren fr die SelektionPlanoperatoren fr die SelektionPlanoperatoren fr ModifikationenPlanoperatoren auf mehreren TabellenPlanoperatoren auf mehreren TabellenPlanoperatoren auf mehreren TabellenPlanoperatoren auf mehreren TabellenbersichtPlanoperatoren fr den VerbundPlanoperatoren fr den VerbundNested-Loop-VerbundNested-Loop-VerbundSort-Merge-VerbundHash-VerbundHash-VerbundHash-VerbundSimple Hash JoinGrace JoinHybrid Hash JoinNutzung typbergreifender ZugriffspfadeVerbundalgorithmen VergleichVerbundalgorithmen in verteilten DBSVerbundalgorithmen in verteilten DBSSemi-VerbundBit-Vektor-VerbundbersichtMengenoperationenBinre Matching-OperationenBinre Matching-OperationenZusammenfassungLiteratur zu diesem Kapitel

chapter08.pdf

Documents

Transcript of chapter08.pdf