Inhalt
Ein Beispiel:
SQL> create type telefonlistentyp is table of varchar2(20);
2 /
Typ wurde erstellt.
SQL> create table mitarbeiter
2 (name varchar2(20), telefon telefonlistentyp)
3 nested table telefon store as telefontab tablespace users;
Tabelle wurde erstellt.
SQL> insert into mitarbeiter values
2 ('Susi',telefonlistentyp('030/12345','030/45678'));
SQL> insert into mitarbeiter values
2 ('Helga',telefonlistentyp('040/4321','040/8765'));
Selects auf nested tables
Beim Selektieren behalten wir im Hinterkopf, dass die Spalte telefon selbst wieder eine Tabelle ist. Ein ordinäres SELECT * FROM MITARBEITER zeigt, dass es sich um einen komplexen Datentyp handelt: wir sehen den Konstruktor (den Typ) und die enthaltenen Daten. Sieht nicht schön aus.
Weil die Spalte telefon eine nested table ist, können wir die darin enthaltenen Daten wie eine normale Tabelle anzeigen mit folgendem select. Wichtig ist, dass der Tabellenausdruck nur eine nested table zurückliefern darf.
select * from table(select telefon from mitarbeiter where name = 'Susi');
Um alle Telefonnummern von allen Mitarbeitern zu erhalten, benötigen wir eine andere Technik, die als „unnesting collections“ bezeichnet wird: Wir müssen die Tabelle dazu mit der nested table joinen:
SQL> select name, ttab.* from mitarbeiter mtab, table(mtab.telefon) ttab;
NAME COLUMN_VALUE -------------------- -------------------- Susi 030/12345 Susi 030/45678 Helga 040/4321 Helga 040/8765
Es handelt sich hierbei wirklich um einen Join, es gilt also das typische Verhalten bzgl. Inner und outer joins. Hier ein Beispiel:
SQL> insert into mitarbeiter values
2 ('Helmut',telefonlistentyp());
SQL> insert into mitarbeiter values
2 ('Willi',null);
SQL> insert into mitarbeiter values
('Jochen',telefonlistentyp(null));
SQL> select name, ttab.* from mitarbeiter mtab,
2 table(mtab.telefon) ttab;
NAME COLUMN_VALUE -------------------- -------------------- Susi 030/12345 Susi 030/45678 Helga 040/4321 Helga 040/8765 Jochen
Susi und Helga sind klar, Jochen wird ausgegeben, denn er hat eine Telefonnummer: NULL. Für Willi ist keine Telefonliste hinterlegt, und Helmuts Telefonliste ist leer. Sollen diese Mitarbeiternamen trotzdem ausgegeben werden, muß ein OUTER JOIN her. Syntax:
SQL> select name, ttab.* from mitarbeiter mtab, table(mtab.telefon)(+) ttab;
Hierbei ist interessant, dass die neue Join-Syntax mit dem Schlüsselwort JOIN nach meiner Kenntnis nicht unterstützt wird, sondern die „alte“ (+)-Notation verwendet werden muss. Beim Versuch, die neue Join-Syntax zu verwenden, stoßen wir auf die in meinem Beispiel nicht vorhandene Verknüpfungsbedingung, die im ON, bzw. USING Teil des Joins benutzt werden muß. Verwenden wir anstelle dessen einen Cross-Join, erhalten wir auch nicht das gewünschte Ergebnis. Anstelle dessen würden alle Telefonnummern mit allen Namen verknüpft werden.
Update auf nested tables
Bei einem Update müssen wir immer unterscheiden, ob wir die Spalte, die eine nested table ist, updaten wollen oder ob wir Inhalte der nested table updaten wollen. Der erste Fall ist simpel, wir updaten die Spalte und verwenden wieder den Konstruktor, – sprich Datentyp -, um der nested-table-Spalte eine neue nested table zuzuweisen (auweia, ich hoffe, ich drücke mich verständlich aus). Beispiel: elga zieht von Hamburg nach München:
SQL> update mitarbeiter
2 set telefon = telefonlistentyp('089/12345','089/6789')
3 where name = 'Helga';
Der zweite Fall, nämlich das verändern einer Telefonnummer vom Helga, ist syntaktisch schon wieder anders. Wir müssen wieder im Hinterkopf behalten, dass es sich bei einer nested table auch um eine ordinäre Tabelle handelt. Und diese gilt es zu referenzieren. Nehmen wir also an, eine von Helgas Telefonnummern ändert sich von München nach Stuttgart:
SQL> update table(select telefon from mitarbeiter where name = 'Helga') 2 set column_value='0711/12345' where column_value='089/12345';
Insert auf nested tables
Nun denn, das nächste Statement: Wie sorge ich dafür, dass Helga auch unter einer dritten Telefonnummer erreichbar ist. Wieder gilt, wir fügen nicht in die Mitarbeitertabelle einen Datensatz ein, sondern in die Spalte, die Teil der Mitarbeitertabelle ist. Dabei muss natürlich klar sein, zu welchem Datensatz die nested table gehört. In meinem Beispiel ist Helga jetzt auch wieder in Berlin erreichbar.
SQL> insert into table(select telefon from mitarbeiter where name = 'Helga')
values ('030/12334');
Delete auf nested tables
Und damit sind wir wieder beim letzten der DML-Statements. Um aus einer nested table einen Datensatz zu entfernen, gilt im Prinzip das gleiche wie oben: wir löschen keinen Datensatz der übergeordneten Tabelle, sondern nur einen der untergeordneten, der nested table. Daher das gleiche Spiel wie bisher:
SQL> delete from table(select telefon from mitarbeiter where name = 'Helga')
where column_value like '089/%';
Zählen der Zeilen in nested tables
Um die Anzahl Datensätze/Werte in einer nested table zu zählen, gibt es zwei Möglichkeiten: Zum einen steht uns natürlich Standard-SQL, und damit auch die COUNT-Funktion zu Verfügung. Jochen hat eine Telefonnummer (die null ist, aber mitgezählt wird), Willi hat keine Telefonliste, und Helmut hat eine, die keine Datensätze enthält. Darum liefert COUNT auf Willis und Helmuts Telefonnummern jeweils numerisch null.
SQL> select count(*) from 2 table(select telefon from mitarbeiter 3 where name='Jochen'); COUNT(*) ---------- 1 SQL> select count(*) from 2 table(select telefon from mitarbeiter 3 where name='Willi'); COUNT(*) ---------- 0
Eine zweite Variante ist, die Funktion CARDINALITY zu benutzen, die die Anzahl Elemente einer nested table zählt. Das Ergebnis unterscheidet sich aber vom COUNT, denn CARDINALITY liefert bei einer leeren nested table (wie bei Helmut) numerisch null, ist jedoch gar keine nested table vorhanden (wie bei Willi), liefert sie NULL.
SQL> select name, cardinality(telefon) from mitarbeiter; NAME CARDINALITY(TELEFON) -------------------- -------------------- Susi 2 Helga 2 Helmut 0 Willi Jochen 1
Nehmen wir an, ein Mitarbeiter hat die gleiche Telefonnummer mehrfach bekommen, wir wollen nur das einfache Auftreten zählen. Gibt es eine Art DISTINCT auch bei nested tables? Ja, hier benutzen wir die Operation SET, um aus einer nested table mit mehrfach vorkommenden Werten eine „echte“ Menge zu machen und doppelte Werte zu verwerfen.
SQL> select name, cardinality(set(telefon)) from mitarbeiter;
Bei Performance-Vegleichen Nested Table vs. zwei „normalen“ Tabellen liess sich allerdings kein Performance-Gewinn feststellen, sondern eher ein Verlust an Performance beim Einsatz von nested tables. Allerdings wurde nicht mit Indexes experimentiert. Ich hätte erwartet, dass die Performance zumindest gleich bleibt, denn schliesslich macht ORACLE intern ja einen Join auf die Tabelle die die nested table Spalte enthält. Aber nein, es wurde sogar schlechter, obwohl der Ausführungsplan gleich war (HASH JOIN in Ermengelung eines Index).
Insofern bleibt die nested table wohl vorerst ein Modellierungs- und Abstraktionselement, ohne dass ein echter Vorteil beim Einsatz von nested tables rausspringt. Beim Einsatz von PL/SQL-Programmen, die die nested tables verarbeiten sollen, sehe ich das wieder anders: Hier existieren interessante Methoden, um Mengenoperationen mit Programmvariablen vorzunehmen. Damit erhält der Mengen-Gedanke auch Einzug in PL/SQL.