Nested Table, Tabellenfunktionen

4. Objektrelationales Typsystem
Kollektionstypen
Nested Table
• Bei einer Nested Table handelt es sich um eine Tabelle als Attributwert.
• Im Gegensatz zu Varray gibt es keine Beschränkung bei der Größe.
• Definition erfolgt auf einem Basistyp, als Basistypen zulässig sind beliebigige Typen.
• Eine Tabelle im klassischen Sinn entsteht bei einem Objekttyp (später) als Basistyp.
• Nested Tables sind bspw. sinnvoll, wenn Mengen kleiner Kardinalität als Attributwert gespeichert werden sollen (Alternative: weitere Tabelle mit Verbund), damit
aber Aufhebung der 1. Normalform
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
150
4. Objektrelationales Typsystem
Kollektionstypen
Typdefinition für Nested Table
create type typname as table of Basisdatentyp
Der so definierte Typ darf natürlich auch für andere Typdefinitionen verwendet werden,
insbesondere Objekttypen.
create t_phoneset as table of varchar2(20);
create type t_zeitreihe as varray(30) of number;
create type zrnested as table of t_zeitreihe;
create type zrnested2 as table of zrnested;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
151
4. Objektrelationales Typsystem
Kollektionstypen
Tabellendefinition mit Nested Table
• Nested Tables werden nicht in den Zeilen einer gewöhnlichen Tabelle gespeichert.
• Dies erfordert die Umsetzung auf eine andere Tabllen für Attribute mit Nested Table
Typ.
create table tabellenname (
...
attribut nestedTableTyp ,
...
) nested table attribut store as nestedTabelle
Wirkung: Die Daten des Attributs attribut werden in einer Tabelle mit dem Namen
nestedTabelle abgelegt.
Diese Tabelle ist nur intern vorhanden, Abfragen auf diese Tabelle sind nicht möglich.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
152
4. Objektrelationales Typsystem
Kollektionstypen
Beispiel:
create table customer (
id char(6) not null primary key,
name varchar2(50) not null,
phones t_phoneset
) nested table phones store as phone_nested;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
153
4. Objektrelationales Typsystem
Kollektionstypen
Indexerzeugung für Nested Table Tabellen
• Auch wenn eine Tabelle zur Speicherung von Nested Tables nicht direkt zugreifbar
ist, kann es sinnvoll sein, auf solch einer Tabelle einen Index zu definieren.
• Grund: Implizit muss ein Verbund (Join) zwischen den Zeilen der eigentlichen Tabelle und den Zeilen der Nested Table Tabelle hergestellt werden.
• Dieser Verbund kann durch einen Index beschleunigt werden.
• Die Tabelle zur Speicherung des Nested Tables hat eine Pseudospalte:
NESTED TABLE ID
create index indexname on nestedTabelle (NESTED TABLE ID)
Beispiel:
create index customer_phones_index on phone_nested(NESTED_TABLE_ID);
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
154
4. Objektrelationales Typsystem
Kollektionstypen
Konstruktoren
Wie bei Varrays stehen nach der Typdefinition Konstruktoren mit beliebiger Stelligkeit
zur Verfügung.
insert into customer
values (1, ’Rosi Mueller’, t_phoneset(’089/32168’));
insert into customer
values (2, ’Jupp Schmitz’, t_phoneset(’0815/123123’, ’0172/1236969’));
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
155
4. Objektrelationales Typsystem
Kollektionstypen
Tabellenfunktion THE()
• In bestimmten Situationen muss ein einzelner Nested Table Wert wie eine eigene
Tabelle behandelt werden.
• Hierzu dient die Tabellenfunktion THE()
• Ohne THE() nur Zugriff auf die Nested Table als Ganzes.
• Schema: THE( nestedTable )
• nestedTable kann bspw. über eine Selektion bestimmt sein.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
156
4. Objektrelationales Typsystem
Kollektionstypen
Einsatzszenarien für THE()
Einfügen eines Werts/Tupels in eine Nested Table:
insert into THE(select phones from customer where id = 4711)
values (’0172/1717170’);
Direkter Zugriff auf die Werte einer Nested Table:
select *
from THE (select phones from customer where id = 4711)
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
157
4. Objektrelationales Typsystem
Kollektionstypen
Tabellenfunktion MULTISET()
• Typische Verwendung in Verbindung mit CAST
• Wandelt eine Ergebnisrelation in einen Nested Table Typ
• CAST( MULTISET( ergebnisrelation ) AS nestedTableTyp )
Beispiel: Ergebnis eines Select soll als Nested Table verwendet werden:
update customer
set phones =
CAST(
MULTISET( select phonenr from phonetable where cid = 4711 )
AS t_phoneset
)
where id = 4711
Auch die Übernahme aus Varrays ist möglich, mit Hilfe eines einfachen Cast:
CAST ( varray AS nestedTableTyp )
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
158
4. Objektrelationales Typsystem
Kollektionstypen
Tabellenfunktionen
• Eine Tabellenfunktion ist eine UDF, die eine Tabelle statt eines skalaren Wertes als
Ergebnis liefert.
• Tabellenfunktionen dienen u.a. dazu, Daten, die ausserhalb der Datenbank liegen,
in relationaler Form zur Verfügung zu stellen.
• Durch die Bereitstellung einer Tabellenfunktionen können solche Daten mit Daten
innerhalb einer Datenbank verknüpft und mittels SQL analysiert werden.
• Damit läßt sich die Ausdruckskraft von SQL auf Daten aus ganz unterschiedlichen
Quellen anwenden.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
159
4. Objektrelationales Typsystem
Kollektionstypen
Nutzung von Tabellenfunktionen in SQL
• Tabellenfunktion werden in der FROM-Klausel einer SQL-Anfrage verwendet (evtl.
auch Unterabfragen).
• Die Syntax lautet:
FROM TABLE( tabfunktion( para1, ... ) ) [tabellenname ]
• Damit wird durch tabellenname die Ergebnisrelation des Aufrufs der Tabellenfunktion tabfunktion mit den angegebenen Parametern referenziert.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
160
4. Objektrelationales Typsystem
Kollektionstypen
Beispiel für Einsatz
• Die UDF passwd() sei eine Tabellenfunktion, die Informationen über die Benutzer
eines Rechnersystems bereitstellt (siehe /etc/passwd).
• Informationen über den Benutzer scott könnten dann mit der folgenden SQLAnfrage ermittelt werden:
SELECT *
FROM TABLE( passwd )
WHERE name = ’scott’
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
161
4. Objektrelationales Typsystem
Kollektionstypen
Deklaration und Implementierung von Tabellenfunktionen
Zur Deklaration einer Tabellenfunktion wird eine create function-Anweisung mit kleinen Unterschieden benutzt:
• Hinter dem Rückgabetyp im Kopf der Funktion wird durch PIPELINED angegeben.
• Obwohl es sich um eine Funktion handelt, muss hinter einer RETURN-Anweisung im
Rumpf kein Wert stehen.
• Tupel der Ergebnisrelation werden durch PIPE ROW(tupel ) definiert.
• Die Tabellenfunktion darf SELECT-Anweisungen benutzen.
☞ Beispiel
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
162
4. Objektrelationales Typsystem
Kollektionstypen
Weitere Einsatzszenarien für Tabellenfunktionen
• Mengenwertige UDFs
• Einfache Schnittstelle für zusätzliche Indextechnologie
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
163
4. Objektrelationales Typsystem
Kollektionstypen
Beispiel: Mengenwertige UDFs
Mit einer kleinen Änderung kann der Algorithmus zur Berechnung des LevenshteinDistanz (vgl. Folie 109) auch für das approximative Pattern Matching eingesetzt werden (vgl. Folie 100).
Es sei M LEV definiert durch:
M LEV [i, j] := min {lev(pat[1 . . . i], text[l . . . j])}
1≤l≤j
d.h., M LEV [i, j] ist die kleinste Distanz zwischen pat[1 . . . i] und einem Suffix von
text[1, j].
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
164
4. Objektrelationales Typsystem
Kollektionstypen
Es gilt nun: M LEV [0, j] = 0 für 0 ≤ j ≤ n,
denn pat[1 . . . 0] = ǫ und ǫ ist stets in text[1 . . . j] ohne Fehler enthalten.
Ansonsten berechnet sich M LEV [i, j] wie LEV [i, j], d.h.:
• M LEV [i, 0] = i für 0 ≤ i ≤ m
• M LEV [i, j] =
min{ M LEV [i − 1, j] + 1,
M LEV [i, j − 1] + 1,
M LEV [i − 1, j − 1] + δ(x[i], y[j])}
Gilt nun M LEV [m, j] ≤ k, so endet in Position j ein Substring y von text mit
lev(pat, y) ≤ k (wobei m die Patternlänge ist).
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
165
4. Objektrelationales Typsystem
Kollektionstypen
Beispiel: Die Tabelle M LEV
ACEABP CQDEABCR.
i
0
1
2
3
4
5
=
für pat
ABCDE
und text
=
j
0
1
A
2
C
3
E
4
A
5
B
6
P
7
C
8
Q
9
D
10
E
11
A
12
B
13
C
14
R
A
B
C
D
E
0
1
2
3
4
5
0
0
1
2
3
4
0
1
1
1
2
3
0
1
2
2
2
2
0
0
1
2
3
3
0
1
0
1
2
3
0
1
1
1
2
3
0
1
2
1
2
3
0
1
2
2
2
3
0
1
2
3
2
3
0
1
2
3
3
2
0
0
1
2
3
3
0
1
0
1
2
3
0
1
1
0
1
2
0
1
2
1
1
2
Für k = 2 ergeben sich die Positionen 3, 10, 13 und 14. Die zugehörigen Substrings
von text sind ACE, ABPCQDE, ABC und ABCR.
Bemerkung: Für m = |pat| und n = |text| ist approximatives Pattern-Matching in
Zeit O(mn) möglich.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
166
4. Objektrelationales Typsystem
Kollektionstypen
Umsetzung in eine Tabellenfunktion
Typ für die Zeilen der Tabellenfunktion definieren:
create type t_approxmatch_row as object (
position number,
substring varchar2(100)
);
Typ für das Ergebnis der Tabellenfuntion definieren:
create type t_approxmatch_table as table of t_approxmatch_row;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
167
4. Objektrelationales Typsystem
Kollektionstypen
Tabellenfunktion definieren:
create function approxmatch(pat in varchar2, text in varchar2, k in integer)
return t_aproxmatch_table pipelined is
pos
integer;
substring varchar2(100);
match t_approx_match_row := t_approx_match_row(null,null);
begin
...
/* Wenn Match gefunden */
match.position := pos;
match.substring := substring;
pipe row(match);
...
return;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
168
4. Objektrelationales Typsystem
Kollektionstypen
Nutzung: Bestimmung aller Textstellen einer Menge von Texten, die approximativ auf
ein Muster mit Abstand ≤ 2 passen:
select t.id, match.*
from texte t, table(approxmatch(’abcde’,t.text,2)) match
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
169