SQL Model Clause Tutorial, Teil 1
Eine Übersetzung aus dem Englischen. Original von 
Rob van Wijk
Mit der SQL Model Clause kann man eine oder meherere Matrices mit einer variablen Anzahl von Dimensionen erstellen. Dies nennt man das Model (Muster/Vorlage). Das Model verwendet eine Untermenge der verfügbaren Spalten aus der FROM Klausel. Es enthält mindestens eine Dimension, mindestens einen Größenwert (Measure) und optional eine oder mehrere Partitionen. Man kann sich das Model als Tabellenkalkulation vorstellen mit mehreren verschiedenen Arbeitsblättern für jeden berechneten Wert (Measure). Ein Arbeitsblatt hat eine X- und eine Y-Achse (2 Dimensionen) welches wiederum in mehrere Teile aufgespalten ist, jedes für ein Land oder eine Abteilung (Partition).
Die Abbildung zeigt ein Model der bekannten EMP Tabelle, bei der deptno eine Partition ist, empno eine Dimension und sal und comm sind 2 Größenwerte.
Sobald das Model aufgesetzt ist werden die Regeln definiert, mit denen die Größen verändert werden. Diese Regeln sind der Kern der Model Clause. Mit wenigen Regeln ist man im Stande komplexe Berechnungen auf den Daten durchzuführen und auch neue Zeilen zu erzeugen. Die Größen sind jetzt Arrays, die über die Dimensionsspalten indiziert sind, und bei denen die Regeln auf alle Partitionen des Arrays angewandt werden. Nachdem die Regeln angewandt wurden wird das Model wieder in traditionelle Zeilen umgewandelt.
Nach meiner Erfahrung ist das Diagramm zur Model Clause in der Oracle Dokumentation sehr komplex und schreckt Entwickler tendenziell ab. Diesen Eindruck möchte ich hier vermeiden und verfolge daher einen andern Ansatz indem ich viele kleine Beispiele auf Basis der EMP und DEPT Tabellen verwende, angefangen von den einfachen und schrittweise darauf aufbauend. Am Ende dieses Kapitels gibt es einen Link zu einem Skript das man auf der eigenen Datenbank ausführen kann.
SQL> select empno
2 , ename
3 , sal
4 from emp
5 where deptno = 10
6 /
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
3 Zeilen ausgewählt.
Das ist der bekannte Inhalt der Tabelle EMP für die Abteilung 10. Die äquivalente SQL Anweisung mit Model Clause, die nichts tut:
SQL> select empno
2 , ename
3 , sal
4 from emp
5 where deptno = 10
6 model
7 dimension by (empno)
8 measures (ename, sal)
9 rules
10 ()
11 /
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
3 Zeilen ausgewählt.
Hier haben wir 2 Größen, ename und sal, und eine Dimension empno. Die Kombinationen aus allen Partitionen und Dimensionsspalten müssen eindeutig sein. Dies wird zur Laufzeit geprüft und, wenn es nicht erfüllt ist, wird ein ORA-32638 geworfen. Wegen der 2 Größen werden intern 2 eindimensionale Arrays gebildet, die mit empno indiziert werden. In Zeile 9 steht das Schlüsselwort „RULES“, aber zunächst ohne Inhalt. Das ist die Stelle an der die Regel für die Berechnungen und das Erstellen neuer Zeilen definiert werden wird. Das Schlüsselwort „RULES“ ist optional, aber um der Klarheit willen werde ich es jedes Mal dazuschreiben. Nach dem Modellieren werden alle Partitionen, Dimensionen und Größen zurückkonvertiert in Spalten in traditionellen Zeilen. Dies bedeutet, dass man nur Spalten in der SELECT Liste angeben kann, die im Model erscheinen. Wenn ich z.B. die Spalte ename nicht als Größe angebe erhalte ich diesen Fehler
SQL> select empno
2 , ename
3 , sal
4 from emp
5 where deptno = 10
6 model
7 dimension by (empno)
8 measures (sal)
9 rules
10 ()
11 /
, ename
*
FEHLER in Zeile 2:
ORA-32614: Unzulässiger MODEL SELECT-Ausdruck
Im nächsten Beispiel erzeuge ich eine Zeile:
SQL> select empno
2 , ename
3 , sal
4 from emp
5 where deptno = 10
6 model
7 dimension by (empno)
8 measures (ename,sal)
9 rules
10 ( ename[7777] = 'VAN WIJK'
11 )
12 /
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
7777 VAN WIJK
4 Zeilen ausgewählt.
Die Regel in Zeile 10 zeigt wie der Wert ename erweitert wird mit der Dimension 7777 und den Wert ‘VAN WIJK’ erhält. Hätte die Tabelle EMP bereits eine empno 7777 enthalten, dann wäre der existierende ename Wert für die empno durch diese Regel überschrieben worden. Da 7777 nicht in EMP existiert wird eine neue elle generiert, die sich als neue Zeile in der Ergebnismenge zeigt. Beachte bitte, dass die Zeile nicht in die Tabelle eingetragen wird, sondern nur in die Ergebnismenge der Abfrage. Durch das Hinzufügen einer zweiten Regel kann auch die sal Spalte gefüllt werden:
SQL> select empno
2 , ename
3 , sal
4 from emp
5 where deptno = 10
6 model
7 dimension by (empno)
8 measures (ename,sal)
9 rules
10 ( ename[7777] = 'VAN WIJK'
11 , sal[7777] = 2500
12 )
13 /
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
7777 VAN WIJK 2500
4 Zeilen ausgewählt.
Die Abfrage liefert sowohl die existierenden Zeilen, als auch die neuen. Mit den Schlüsselwörtern “RETURN UPDATED ROWS” hast du die Möglichkeit nur die neuen und geänderten Zeilen zurückzugeben:
SQL> select empno
2 , ename
3 , sal
4 from emp
5 where deptno = 10
6 model
7 return updated rows
8 dimension by (empno)
9 measures (ename,sal)
10 rules
11 ( ename[7777] = 'VAN WIJK'
12 , sal[7777] = 2500
13 )
14 /
EMPNO ENAME SAL
---------- ---------- ----------
7777 VAN WIJK 2500
1 Zeile wurde ausgewählt.
Alle Berechnungen werden auf jede Partition angewandt. Wir können dies sehen wenn wir das Filterpredicate “deptno = 10” löschen und stattdessen die deptno Spalte anzeigen. Jetzt stellt sich die Frage ob deptno als eine Partition oder eine Größe verwendet werden soll. Probieren wir zunächst aus was passiert, wenn wir deptno als Größe definieren.
SQL> select empno
2 , ename
3 , sal
4 , deptno
5 from emp
6 model
7 return updated rows
8 dimension by (empno)
9 measures (ename,sal,deptno)
10 rules
11 ( ename[7777] = 'VAN WIJK'
12 )
13 /
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7777 VAN WIJK
1 Zeile wurde ausgewählt.
Wie erwartet wird eine Zeile erstellt mit NULL als deptno Wert. Jetzt erforschen wir, was passiert wenn wir deptno als Partition definieren:
SQL> select empno
2 , ename
3 , sal
4 , deptno
5 from emp
6 model
7 return updated rows
8 partition by (deptno)
9 dimension by (empno)
10 measures (ename,sal)
11 rules
12 ( ename[7777] = 'VAN WIJK'
13 )
14 /
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7777 VAN WIJK 30
7777 VAN WIJK 20
7777 VAN WIJK 10
3 Zeilen ausgewählt.
Ein klarer Unterschied. In der Tabelle EMP gibt es nur die deptno Werte 10, 20 und 30 und deshalb hat das Modell effektiv drei Partitionen. Die Regel wird auf alle drei Partitionen angewandt und resultiert im Erzeugen von drei neuen Zeilen. Bisher haben dich die Beispiele vielleicht noch nicht davon überzeugt die Modell Klausel zu verwenden. Es wurden eine Menge zusätzliche SQL-Zeilen eingeführt um relativ einfache Ergebnisse zu erzielen. Mit der kreativen Nutzung von UNION ALL und DUAL Tabelle hätten wir das Gleiche erreichen können. Die Absicht in diesem ersten Teil ist nur die Grundlagen zu zeigen. Im nächsten Teil werde ich multi cell Referenzen, Referenz Modelle und Iterationen zeigen. Und das ist es wo es interessant wird.
oracle@matzberger.de Für Kontakte aller Art, auch Fehlermeldungen zum Tutorial
Impressum