Datenbanksysteme I - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. Werner Kießling
M. Endres, A. Huhn, T. Preisinger
WS 2005/2006
16. Jan. 2006
Lösungsblatt 10
Datenbanksysteme I
Aufgabe 1: Einrichten einer DB für Query-Optimierung
Kein Lösungsvorschlag notwendig.
Aufgabe 2: Algebraische Query-Optimierung
Lösungsvorschlag:
a) SQL-Statement in relationaler Algebra
πg.ngc, g.messier, g.sternbildname (σg.sternbildname = s.name ∧
s.name = sb.sternbildname ∧ g.kategorie=’Galaxie’ (g × s) × sb)
b) Hill-Climbing-Algorithmus:
Initaler und optimierter Operatorbaum:
c) Zwischenergebnisse:
Anzahl der Elemente:
Anzahl der Elemente in der Relation Galaxie: 98
Anzahl der Elemente in der Relation Sternbild: 86
Anzahl der Elemente in der Relation Sternbildbeschreibung: 12
Ohne Optimierung:
Nach g × s: 8428
Nach (g × s) × sb: 101136
Nach der Selektion: 15
Mit Optimierung:
Selektion auf g: 38
Equijoin mit s: 38
1
Equijoin mit sb: 15
In der Ergebnismenge sind die Galaxien enthalten, die in einem Sternbild mit Beschreibung
liegen und tatsächlich Galaxien sind.
Aufgabe 3: Ausführungsplan in Oracle
EXPLAIN PLAN und plan table.
a) Erstellen der plan table:
CREATE TABLE plan_table
(statement_id
VARCHAR2(30),
timestamp
DATE,
remarks
VARCHAR2(80),
operation
VARCHAR2(30),
options
VARCHAR2(30),
object_node
VARCHAR2(128),
object_owner
VARCHAR2(30),
object_name
VARCHAR2(30),
object_instance NUMERIC,
object_type
VARCHAR2(30),
optimizer
VARCHAR2(255),
search_columns
NUMERIC,
id
NUMERIC,
parent_id
NUMERIC,
position
NUMERIC,
cost
NUMERIC,
cardinality
NUMERIC,
bytes
NUMERIC,
other_tag
VARCHAR2(255),
other
LONG);
b) Query ausführen:
EXPLAIN PLAN
SET STATEMENT_ID = ’Optimierung’
FOR
SELECT g.ngc, g.messier, g.sternbildname
FROM Galaxie g, Sternbild s, Sternbildbeschreibung sb
WHERE g.sternbildname = s.namedeutsch
AND s.namedeutsch = sb.sternbildname
AND g.kategorie=’Galaxie’;
c) Anfrage an die plan table:
SELECT operation, options, object_name, id, parent_id
FROM plan_table
WHERE statement_id=’Optimierung’
Ergebniss:
2
OPERATION
SELECT STATEMENT
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
INDEX
INDEX
OPTIONS
null
null
null
FULL
UNIQUE SCAN
UNIQUE SCAN
OBJECT NAME
null
null
null
GALAXIE
SYS C0025960
SYS C0025961
ID
0
1
2
3
4
5
PARENT ID
null
0
1
2
2
1
Graphische Darstellung:
SELECT Statement
NESTED
LOOPS
VVVV
i
VVVV
iiii
i
i
VVVV
i
VVVV
iii
i
i
i
VV
i
i
i
Zugriff
mit Index (SB)
NESTED
LOOPS
TTTT
jj
j
T
j
T
j
T
TTTT
jjj
TTTT
jjjj
j
TT
j
j
j
TABLE ACCESS
Zugriff mit Index (S)
Galaxie
Interpretation: Der Zugriff erfolgt, sofern möglich, mit den Primärschlüssel-Indizes. Die EquiJoins werden als NESTED LOOPS durchgeführt. Die Reihenfolge der Operationen entspricht
dem optimierten Operatorbaum.
Aufgabe 4: Erstellen eines Index - Performancemessung
a) Erstellen und Füllen der Relation Kart Produkt:
CREATE TABLE Kart_Produkt(
ngc integer,
messier integer,
G_sternbildname varchar2(50),
kategorie varchar2(200),
namedeutsch varchar2(50),
namelateinisch varchar2(50),
abkuerzung varchar2(5),
SB_sternbildname varchar2(50)
);
INSERT INTO Kart_Produkt
SELECT g.ngc, g.messier, g.sternbildname,
g.kategorie, s.namedeutsch, s.namelateinisch,
s.abkuerzung, sb.sternbildname
FROM Galaxie g, Sternbild s, Sternbildbeschreibung sb
b) SQL-Statement
3
1) Die Query benötigt im Mittel etwa 15 Millisekunden.
2) Der Index kann auf kategorie oder messier eingerichtet werden, da diese Attribute in der
WHERE-Klausel abgefragt werden. Wird auf beiden Attributen jeweils ein Index erstellt,
verlangsamen diese aufgrund eines komplexeren Ausführungsplans die Query.
CREATE INDEX kategorie_index ON Kart_Produkt(kategorie);
oder
RCEATE INDEX messier_index ON Kart_Produkt(messier);
3) Die Query braucht wiederum ca. 15 Millisekunden, es hat sich also keine Beschleunigung
ergeben.
4) Das Statement mit EXPLAIN PLAN:
EXPLAIN PLAN
SET STATEMENT_ID = ’Kart_Prod’
FOR
SELECT ngc, messier, G_sternbildname
FROM Kart_Produkt
WHERE Kategorie=’Diff. Nebel’
AND messier < 60;
Die Anfrage an die plan table:
SELECT operation, options, object_name
FROM plan_table
WHERE statement_id=’Kart_Prod’
und das Ergebnis:
OPERATION
SELECT STATEMENT
TABLE ACCESS
OPTIONS
null
FULL
OBJECT NAME
null
KART PRODUKT
Oracle entscheidet automatisch, dass der Index nicht verwendet werden soll und die Relation
FULL gelesen wird. Andernfalls würde an dieser Stelle der Relation ein INDEX auftauchen,
beispielsweise, wenn Oracle den PRIMARY KEY-Index verwendet. Da Oracle den Index
nicht verwendet, entsteht auch kein Unterschied in der Ausführungsdauer der Query.
Aufgabe 5: Vertauschung von Selektion und Natural Join
Zu zeigen war:
σr ∧ s (R ./ S) = σr (R) ./ σs (S)
Beweis:
σr∧s (R ./ S) = σr (σs (R ./ S)) = σr (R ./ σs (S)) = σr (R) ./ σs (S)
4