select

Architektur von Datenbanksystemen II
Aufgabe der Anfrageverarbeitung
 Abbildung auf die inhaltliche Adressierung
von Mengen von Sätzen
 Überprüfung der syntaktischen Korrektheit
von Anfragen
 Überprüfung? von Zugriffsberechtigungen und
Integritätsbedingungen
- Referenzielle Integrität, Eindeutigkeits- und Wertebereichszusicherungen, …
 Erzeugung einer optimalen ausführbargen Folge
interner DBS-Operationen
- Anfrageoptimierer ist (im Wesentlichen) für die effiziente
Abarbeitung verantwortlich
2
Anfragespezifikation
 Direkte Adressierung und satzorientierter Zugriff auf die Datensätze, keine mengenorientierte Verarbeitung
 Verantwortung für die Zugriffspfadwahl, d.h. Art und Reihenfolge der Zugriffe liegt beim Programmierer
 Erlauben leichte Abbildung der DML-Befehle auf interne Satzoperationen (1:1)
 Inhaltliche Adressierung und mengenorientierter Zugriff auf die Datensätze, kein Rückgriff auf einzelne Sätze
 Verantwortung für die Zugriffspfadauswahl, d.h. Art und Reihenfolge der Zugriffe liegt beim System, transparent für
den Anwender
 Hohe Auswahlmächtigkeit
- an der Prädikatenlogik erster Stufe orientiert ist
- unabhängige oder korrelierte Teilanfragen zur Bestimmung von Suchargumenten in beliebiger Schachtelungstiefe
zulässig
- zusätzlich den Einsatz von Built-in- und Sortier-Funktionen auf Partitionen der Satzmenge gestattet
 Komplexe Abbildung der deskriptiven Anfrage auf interne Satzoperationen
3
Motivation
 Formale Sprache, mit der sich Anfragen über
einem relationalen Schema formulieren lassen
SQL
SELECT
P_BRAND, O_SHIPPRIORITY,
SUM(L_QUANTITY*L_EXTENDEDPRICE)
AS TURNOVER
FROM
TPCD.LINEITEM, TPCD.ORDERS, TPCD.PART
WHERE L_ORDERKEY = O_ORDERKEY
AND L_PARTKEY = P_PARTKEY
AND O_ORDERSTATUS = ’F’
AND P_CONTAINER = ’LG_BAG’
GROUP BY
P_BRAND, O_SHIPPRIORITY
HAVING
AVG(L_QUANTITY) > 250;
Interndarstellung: Relationale Algebra
5
Motivation





Formale Sprache für den Berechnungsweg von Anfrageergebnissen
Internrepräsentation für DB-Anfragen
Mathematische Rechenregeln ermöglichen Abfrageoptimierung durch algebraische Umformung
Nicht für den Nutzer eines DBMS sichtbar
Auch geeignet zur Formulierung von Integritätsbedingungen
 Relationale Algebra enthält keine Operationen
- zum Erzeugen bzw. Löschen von Relationen erzeugen
- zum Einfügen, Löschen und Verändern von Tupeln
 Ausschließlich „lesenden“ Operationen
 Es wird vorausgesetzt, dass die Relationen der Datenbank schon irgendwie existieren und mit Tupeln
gefüllt worden sind
6
Relationale Algebra
 Gegeben eine Menge N („Anker der Algebra”): Menge der Relationen
 Operationen opj: NkN (Abgeschlossenheit)
 Fünf Basisoperationen
 Gegeben zwei Relationen R(A1,…,Ar) und S(B1,…,Bs) mit Grad r und Grad s dann sind
𝑅′ ≔< 𝑜𝑝 ><𝑃𝑎𝑟𝑎𝑚𝑒𝑡𝑒𝑟> (𝑅) sowie
𝑅′′ ≔ 𝑅 < 𝑜𝑝 ><𝑃𝑎𝑟𝑎𝑚𝑒𝑡𝑒𝑟> 𝑆
wieder Relationen
 Zur Erleichterung der Definitionen sei angenommen, dass die Attribute der Relationen geordnet sind.
Somit gilt:
R = {(a1,…,ar) | ai  dom(Ai), 1  i  r} und S = {(b1,…,bs) | bi  dom(Bi), 1  i  s}
7
Projektion
Sei A' eine Teilmenge der Attribute einer Relation R(A1, ..., An).
Die Projektion der Attribute A' aus einem Tupel t  R ist definiert als das Tupel
𝜋𝐴′ 𝑡 = (𝐴1′ 𝑡 , … , 𝐴′𝑚 𝑡 )
Die Projektion der Attribute A' einer Relation R ist definiert als die Relation
𝜋𝐴′ 𝑅 = {𝜋𝐴′ 𝑡 |𝑡𝜖𝑅}
 Projektion ist eine Operation, die bestimmte Spalten aus einer Relation auswählt und diese als neue
Relation ausgibt
9
Projektion (2)
Studenten
MatrikelNr
Vorname
Vorname2
Geburt
SgNr
Bafoeg
1001
Schmidt
Hans
Peter
24.2.1990
Würzburg
2
200
1002
Meisel
Dirk
Helmut
17.8.1989
Schweinfurt
3
500
1003
Schmidt
Amelie
19.9.1992
Rimpar
1
0
1004
Krause
Christian
3.5.1990
Würzburg
1
100
1005
Schäfer
Julia
30.3.1993
Kitzingen
5
0
1006
Rasch
Lara
30.3.1992
Würzburg
3
0
1007
Bakowski
Juri
15.7.1988
Schweinfurt
4
400
Johannes
𝜋𝑁𝑎𝑚𝑒,𝑂𝑟𝑡 (𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑒𝑛)
Name
Ort
Schmidt
Würzburg
Meisel
Schweinfurt
Schmidt
Rimpar
Krause
Würzburg
Schäfer
Kitzingen
Rasch
Würzburg
Bakowski
Schweinfurt
10
Projektion (4)
 Da Dubletten (identische Tupel) in Relationen nicht vorkommen dürfen, enthält die Projektion i. A.
weniger Tupel als die ursprüngliche Relation!
 Achtung: Das ist in SQL standardmäßig nicht so!
 Wenn A' eine Teilmenge der Attribute von R(A1, ..., An) ist und es gilt
𝜋𝐴′ 𝑅
= |𝑅|
Welche Eigenschaft hat dann A'?
11
Projektion in SQL: SELECT
𝜋𝑁𝑎𝑚𝑒,𝑂𝑟𝑡 (𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑒𝑛)
 Duplikate werden nicht standardmäßig eliminiert
SELECT Name, Ort
FROM Studenten
 Mit Duplikat-Eliminierung
SELECT DISTINCT Name, Ort
FROM Studenten
12
Selektion (Restriktion)
 Die Selektion einer Relation R ist definiert als die Menge aller Tupel aus R, die der
Selektionsbedingung P genügen:
𝜎𝑃 𝑅 = {𝑡|𝑡𝜖𝑅 ∧ 𝑃(𝑡)}
 P setzt sich zusammen aus
- Operanden:
Konstanten oder Name eines Attributs
- Vergleichsoperatoren: = , ≠ , < , ≤ , > , ≥
- Boolesche Operatoren:  ,  , 
 Selektion ist eine Operation, die bestimmte Zeilen aus einer Relation auswählt und diese als neue
Relation ausgibt
13
Selektion: Beispiel
Studenten
MatrikelNr
Name
Vorname
Vorname2
Geburt
Ort
SgNr
Bafoeg
1001
Schmidt
Hans
Peter
24.2.1990
Würzburg
2
200
1002
Meisel
Dirk
Helmut
17.8.1989
Schweinfurt
3
500
1003
Schmidt
Amelie
19.9.1992
Rimpar
1
0
1004
Krause
Christian
3.5.1990
Würzburg
1
100
1005
Schäfer
Julia
30.3.1993
Kitzingen
5
0
1006
Rasch
Lara
30.3.1992
Würzburg
3
0
1007
Bakowski
Juri
15.7.1988
Schweinfurt
4
400
Johannes
𝜎𝑁𝑎𝑚𝑒=′ 𝑆𝑐ℎ𝑚𝑖𝑑𝑡 ′ 𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑒𝑛
MatrikelNr
Name
Vorname
Vorname2
Geburt
Ort
SgNr
Bafoeg
1001
Schmidt
Hans
Peter
24.2.1990
Würzburg
2
200
1003
Schmidt
Amelie
19.9.1992
Rimpar
1
0
14
Selektion in SQL: SELECT mit WHERE
𝜎𝑁𝑎𝑚𝑒=′𝑆𝑐ℎ 𝑚𝑖𝑑𝑡 ′ 𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑒𝑛
SELECT *
FROM Studenten
WHERE Name='Schmidt'
𝜋𝑁𝑎𝑚𝑒,𝑉𝑜𝑟𝑛𝑎𝑚𝑒,𝑂𝑟𝑡 (𝜎𝑁𝑎𝑚𝑒=′𝑆𝑐ℎ 𝑚𝑖𝑑𝑡 ′ 𝑆𝑡𝑢𝑑𝑒𝑛𝑡𝑒𝑛
SELECT Name, Vorname, Ort
FROM Studenten
WHERE Name='Schmidt'
Name
Vorname
Ort
Schmidt
Hans
Würzburg
Schmidt
Amelie
Rimpar
15
Weitere Basisoperationen
𝑅×𝑆
 Das kartesische Produkt zweier Relationen R(A1, A2, ..., An) und S(B1, B2, ..., Bm) ist definiert als Relation
R x S = { (a1, a2, ..., an, b1, b2, ..., bm) |
(a1, a2, ..., an)  R  (b1, b2, ..., bm)  S }
A1
A2
B1
B2
1
A
1
X
2
B
2
Y
3
C
4
Z
x
=
A1
A2
B1
B2
1
A
1
X
1
A
2
Y
1
A
4
Z
2
B
1
X
2
B
2
Y
2
B
4
Z
3
C
1
X
3
C
2
Y
3
C
4
Z
16
Weitere Basisoperationen
𝑅∪𝑆
 R  S := { r | r  R oder r  S }
 R und S besitzen gleiches Relationenschema
(bis auf Umbenennungen identisch)
 Wertebereiche müssen kompatibel oder
vereinigungsverträglich sein!
𝑅−𝑆
 R - S := { r | r  R und r  S }
 R und S besitzen gleiches Relationenschema
(bis auf Umbenennungen identisch)
 Wertebereiche müssen kompatibel oder
vereinigungsverträglich sein!
𝜌𝑆 (𝑅)
 𝜌𝑆 𝑅 : Relation R wird in Relation S
umbenannt
 𝜌𝐵←𝐴 𝑅 : Attribut A der Relation R wird
umbenannt in B
- Umbenennung unterscheidet sich von den
anderen Operatoren dadurch, dass keine
Berechnung vorgenommen wird
- Operator ist aber notwendig, wenn eine
Relation mehrfach in einer Anfrage
vorkommt
17
Beispiele zur Relationalen Algebra
R
A
B
C
a
b
d
c
S
D
E
F
c
b
g
a
a
f
d
a
f
b
d
RS
R–S
RS
a
b
c
d
a
f
a
b
c
b
d
c
b
b
g
a
πA,C(R)
A
C
a
c
d
f
c
d
σB=b(R)
A
B
C
D
E
F
c
a
b
c
b
g
a
d
a
b
c
d
a
f
d
a
f
b
g
a
d
a
f
d
a
f
c
b
d
b
g
a
c
b
d
d
a
f
A
B
C
a
b
c
c
b
d
18
Durchschnitt und Division
𝑅⋂𝑆
 R  S := { r | r  R und r  S }
 Es gilt: 𝑅⋂𝑆 = 𝑅 − 𝑅 − 𝑆
𝑅÷𝑆
 Seien Relation R(A,B), Relation S(B) zwei Relationen, wobei alle Attribute aus S in R enthalten sind
 Die Division 𝑅÷𝑆 ist dann definiert zu
𝑅 ÷ 𝑆 = 𝑡 ∃𝑥, 𝑦 ∈ 𝑅 ∀𝑦 ∈ 𝑆}
 Darstellung des Quotienten durch die Basisoperationen
𝑅 ÷ 𝑆 ≔ 𝜋𝐴−𝐵 𝑅 − 𝜋𝐴−𝐵 ( 𝜋𝐴−𝐵 𝑅 × 𝑆) − 𝑅
20
Natürlicher Verbund
𝑅⋈𝑆
 Wichtigste Operation neben der Selektion
 Voraussetzung
A1 = B1, ..., Ak = Bk und
Aj  Bi für alle j und i mit k < j  r und k < i  s
 Der natürliche Join ist definiert als
𝑅 ⋈ 𝑆 ≔ 𝜋𝑖𝑘+1 ,𝑖𝑘+2 ,….,𝑖𝑟+𝑠 (𝜎𝑅.𝐴1=𝑆.𝐵1∧...∧𝑅.𝐴𝑘=𝑆.𝐵𝑘 (𝑅 × 𝑆))
 Gibt es keine gemeinsames Attribut so ist das
Ergebnis das kartesische Produkt
 Bemerkung: i.A. besitzen die Relationen nur ein
gemeinsames Attribut!!!
R
B C A
S
B
C D
b
c
a
b
c
d
b
c
d
b
c
e
b
f
b
a
d
b
a
d
c
𝑹⋈𝑺
B
C
A
D
b
c
a
d
b
c
a
e
b
c
d
d
b
c
d
e
a
d
c
b
21
Theta- und Equi-Join
𝑅 ⋈𝛩 𝑆
 Auswahl bestimmter Tupel aus dem kartesischen Produkt RS:
𝑅 ⋈𝑖𝛩𝑗 𝑆 ≔ 𝜎𝐴𝑖 Θ𝐵𝑗 (𝑅 × 𝑆)
 Mit   {=, , <, , >, }

 Beispiel: 𝑅 ⋈𝐴=𝐸 𝑆
R
A B C D
E F G
𝑅 ⋈𝐴=𝐸 𝑆 A
B
C
D
E
F
G
2 3 4
1 2 3
1
2
3
4
1
2
3
4 5 6 7
7 8 9
7
8
9
0
7
8
0
1
7
S
8 9 0
22
Verlustfreiheit von Joins
 Eine Join-Operation zwischen R und S heißt verlustfrei, wenn jeder Datensatz aus R und jeder
Datensatz aus S in der Ergebnisrelation enthalten ist.
 Die inverse Operation Projektion erzeugt dann wieder R und S aus dem Join-Ergebnis
 Tupel, denen bei Join-Operationen die entsprechenden Tupel in der anderen
Tabelle fehlen, mit denen sie verknüpft werden können, heißen auch „Dangling Tupel“ bzw.
Datensätze
 Um sie in die Ergebnismenge mit aufnehmen zu können, werden die Outer-Join-Operatoren
benötigt
 Inner Joins sind in der Regel verlustbehaftet!
23
Outer Joins
⟕
 Alle Tupel der linken Relation R, die keinen Join-Partner in der rechten Relation S haben, werden
trotzdem ausgegeben
 Die entsprechenden Spalten für S enthalten NULL-Werte
R
S
A1
A2
1
A
2
B
3
C
B1
⟕
B2
1
X
2
Y
4
Z
A1
=
A2
B1
B2
1
A
1
X
2
B
2
Y
3
C
NULL
NULL
24
Outer Joins (2)
⟖
 Alle Tupel der rechten Relation S, die keinen Join-Partner in der linken Relation R haben, werden
trotzdem ausgegeben
 Die entsprechenden Spalten für R enthalten NULL-Werte
⟗
 Alle Tupel sowohl der linken als auch der rechten Relation, die keinen Join-Partner haben, werden
trotzdem ausgegeben
 Auf beiden Seiten werden die entsprechenden Spalten mit NULL-Werten aufgefüllt
25
Outer Joins (3) in SQL
 RIGHT JOIN (alternativ RIGHT OUTER JOIN)
SELECT *
FROM Studenten RIGHT JOIN Studiengang
ON Studenten.SgNr = Studiengaenge.SgNr
 LEFT JOIN (alternativ LEFT OUTER JOIN)
SELECT *
FROM Studiengaenge LEFT JOIN Studenten
ON Studenten.SgNr = Studiengaenge.SgNr
 FULL OUTER JOIN
- Nicht in allen DB-Systemen verfügbar (z.B. MySQL nicht)
26
Halbverbund
𝑅⋉𝑆
 Sei RS das Schema der Relation R. Dann ist der Semi-Join von R und S definiert als:
R ⋉ S ≔ πR (R ⋈ S)
 Enthält alle Tupel der Relation R, die die Join-Bedingung mit der Relation S erfüllen
27
Beispiele zu Verbundarten
R
A
B
C
a1
b1
a2
b2
C
D
E
A
B
C
D
E
c1
c1
d1
e1
a1
b1
c1
d1
e1
c2
c3
d2
e2
-
-
c3
d2
e2
A
B
C
a1
b1
c1
𝑅⋉𝑆
R⟕S
S
R⟖S
R⟗S
A
B
C
D
E
a1
b1
c1
d1
e1
a2
b2
c2
-
-
A
B
C
D
E
a1
b1
c1
d1
e1
a2
b2
c2
-
-
-
-
c3
d2
e2
28
Joins – Übersicht
29
Zerlegung der Anfrageverarbeitung
 Logischer DB-Prozessor
(System R: relational data system)
 liefert einen Ausführungsplan
(query execution plan; QEP) zur
Übersetzungszeit
 Physischer DB-Prozessor
(System R: relational storage system)
 tatsächliche Ausführung des Anfrageplanes
zur Laufzeit
31
Beispiel
 SELECT R.c, S.c, T.c, sum(T.d)
FROM R, S, T
WHERE R.a = S.a and
S.b = T.b and
R.b > 100 and S.d<100 and T.a <> 500
GROUP BY R.c, S.c, T.c
HAVING COUNT(*) > 20
32
Zerlegung der Anfrageverarbeitung (2)
 Interndarstellung mit Zugriffs- und Integritätskontrolle
- Überprüfung der referenzierten Datenbankobjekte wie Tabellen oder Attribute
- Erweiterung der Anfrage um Operatoren, die bei der Ausführung der Anfrage die strukturelle Konsistenz der
Datenbank sicherstellen bzw. deren Verletzung verhindern
 Anfragerestrukturierung (logische Optimierung)
- Vereinfachung der Anfrage durch algebraische Eigenschaften der Relationenalgebra
- Umformung auf Schemaebene; unabhängig von der konkreten Ausprägung der Datenbank (z.B. unabhängig von
Werteverteilungen, der Existenz von Indexstrukturen etc.)
 Anfragetransformation
- Zuordnung eines Planoperators zu jedem Operator der relationalen Algebra
 Planparametrierung und Codeerzeugung
 Ausführungskontrolle und Ergebnisbereitstellung
33
Phasen der Anfrageverarbeitung
Anfrageoptimierung
Anfrage
Interndarstellung
Zugriffs- &
Integritätskontrolle
Anfragerestrukturierung
Anfragetransformation
Anfrageausführungsplan (QEP)
Ergebnis
Ergebnisbereitstellung
Ausführungskontrolle
Codeerzeugung
Planparametrierung
Logischer
DB-Prozessor
(Anfrageprozessor)
Übersetzungszeit
Laufzeit
Physischer
DB-Prozessor
(Anfrageausführer)
34
Phasen der Anfrageverarbeitung (2)
 Wie ist eine Anfrage intern repräsentiert? -> Operatorengraph
 Lexikalische und syntaktische Analyse
- Überprüfung auf korrekte Syntax (Parsing)
- Erstellung eines Anfragegraphen für die nachfolgenden Übersetzungsschritte (Überführung in Interndarstellung)
 Semantische Analyse
- Feststellung der Existenz und Gültigkeit der referenzierten Relationen und Attribute
- Ersetzen der externen durch interne Namen (Namensauflösung)
- Konversion vom externen Format in interne Darstellung
 Durchführung einfacher Integritätskontrollen (Kontrolle von Formaten und Konversion von Datentypen)
 Generierung von Laufzeitaktionen für werteabhängige Kontrollen
35
Phasen der Anfrageverarbeitung (3)
 Anwendung von heuristischen Regeln
 zielt auf globale Verbesserung des Anfragegraphen
ab
 Berücksichtigung ausführbarer Operationen
 Ersetzung und ggf. Zusammenfassen der logischen
Operatoren durch Planoperatoren
 Auswahl der günstigsten Planalternative
 Generierung eines zugeschnittenen Programms für
die vorgegebene (SQL-) Anfrage
 Erzeugung eines ausführbaren Zugriffsmoduls
 Verwaltung der Zugriffsmodule in einer DBVSBibliothek
 Anfragetransformation, Datenlokalisierung, globale
Optimierung
- meist sind mehrere Planoperatoren als
Implementierung eines logischen Operators verfügbar
- meist sind viele Ausführungsreihenfolgen oder
Zugriffspfade auswählbar
- Bewertung der Kosten und Auswahl des günstigsten
Ausführungsplanes
36
Schwerpunkt: Interndarstellung
Anfrageoptimierung
Anfrage
Interndarstellung
Zugriffs- &
Integritätskontrolle
Anfragerestrukturierung
Anfragetransformation
Anfrageausführungsplan (QEP)
Ergebnis
Ergebnisbereitstellung
Ausführungskontrolle
Codeerzeugung
Planparametrierung
Logischer
DB-Prozessor
(Anfrageprozessor)
Übersetzungszeit
Laufzeit
Physischer
DB-Prozessor
(Anfrageausführer)
37
Interndarstellung
 Strukturelle Betrachtung: Relationen, visualisiert als Tabellen
 Operationelle Betrachtung: Operatoren der Relationalen Algebra
SELECT
FROM
WHERE
AND
AND
Name, Beruf
ABT a, PERS p, PM pm, PROJ pj
a.Anr = p.Anr AND a.Aort = ‘Erlangen’
p.Pnr = pm.Pnr AND pm.Jnr = pj.Jnr
pj.Port = ‘Erlangen’
Selektion - Auswahl von ‚Zeilen‘: pred()(R)
Projektion - Auswahl von ‚Spalten‘: {A1,…,Ak}(R)
Gruppierung - Auswahl von ‚Spalten‘ und Aggregatbildung auf Duplikaten: {G1,…,Gn:A1,…,Ak}(R)
Verbund - Verbinden von Relationen R und S: RP(Ai,Bj)⋈S
auf logischer Ebene: n-äre Verbundoperationen
- Beispiel SQL-Server: ca. 200 logische DB-Operatoren
-
 Umsetzung in relationalen Operatoren
-
effiziente Datenstruktur mit geeigneten Zugriffsfunktionen
prozedurale Darstellung einer deskriptiven, mengenorientierten Anfrage
Knoten sind Operatoren der Relationalen Algebra
Blattknoten sind (üblicherweise) Relationen
gerichtete Kanten repräsentieren den Datenfluss
Operatorengraph
38
Interndarstellung – Beispiele
Projektion
Selektion
SELECT
FROM
WHERE
AND
C_NAME, C_ADDRESS
TPCD.CUSTOMER, TPCD.SUPPLIER
C_NAME = S_NAME
C_MKTSEGMENT = ’MACHINERY’;
SELECT
P_BRAND, O_SHIPPRIORITY,
SUM(L_QUANTITY*L_EXTENDEDPRICE)
AS TURNOVER
FROM
TPCD.LINEITEM, TPCD.ORDERS, TPCD.PART
WHERE L_ORDERKEY = O_ORDERKEY
AND L_PARTKEY = P_PARTKEY
AND O_ORDERSTATUS = ’F’
AND P_CONTAINER = ’LG_BAG’
GROUP BY
P_BRAND, O_SHIPPRIORITY
HAVING
AVG(L_QUANTITY) > 250;
Verbundoperation
Quellrelationen
39