SQL Model Clause Tutorial, Teil 3
Eine Übersetzung aus dem Englischen. Original von 
Rob van Wijk
Die praktischen Seiten der SQL Model Klausel
Wenn das Entwickeln von SQL Abfragen Teil deines Jobs ist, dann ist es wert die SQL Model Klausel zu kennen. Sobald du die erste Furcht vor der neuen Syntax überwunden und die Grundlagen gelernt hast, kannst du dir das Schreiben von prozeduralem Code und das Anlegen von Hilfsobjekten in der Datenbank immer öfter sparen, indem du mehr in SQL selbst tust. Am Ende schreibst du möglicherweise kürzeren, lesbareren und manchmal schnelleren Code. dieser Artikel zeigt ein paar Verwendungen für die Model Klausel für recht häufige Typen von Abfragen und diskutiert jeweils ob die Lösung über die Model Klausel besser ist als die alternativen Abfragen – falls vorhanden – in Bezug auf Lesbarkeit und Performance. Zum Schluss wirst du in deiner täglichen Arbeit leichter erkennen können wo du die Model Klausel anwenden kannst.
Hintergrund / Überblick
Die SQL Model Clause wurde 2003 mit Oracle 10g Release 1 eingeführt. Laut Dokumentation kann die Model Clause PC-basierte Spreadsheets ersetzen, indem sie ein mehrdimensionales Array aus Abfrageergebnissen erzeugt und dann Formeln (Regeln genannt) anwendet um neue Werte zu berechnen. Aber, wenn man die verschiedenen Oracle Foren ansieht, wird sie nicht oft benutzt. Und wenn sie benutzt wird, dann werden keine Spreadsheetberechnungen durchgeführt. Die Typen von Abfragen, für die die Model Clause genutzt wird, werden in diesem Artikel diskutiert. Die Abfragetypen sind:
- Vorhersageabfragen
- Zeilengenerierung
- Berechnungen basierend auf berechneten Werten
- Komplexe Algorithmen
- Zusammenfassen von Zeichenketten (String Aggregation)
Natürlich sind die Möglichkeiten der Model Clause nicht auf diese Liste beschränkt.
Wenn dir die SQL Model Clause noch nicht geläufig ist, dann kannst du dies hier Chapter 23 des Data Warehousing Guide nachholen, oder auch in den Teilen 1 und 2 dieses Tutorials.
Vorhersageabfragen
Dieser Typ von Abfragen wird durchweg im zuvor erwähnten Kapitel 23 des Oracle Database Data Warehousing Guide verwendet. Die Basis für diesen Abfragetyp ist typischerweise eine Verkaufs-Tabelle oder -View und die Frage, die zu beantworten ist, ist eine Vorschau wie zukünftige Verkäufe aussehen werden. Sehen wir uns zum Beispiel die Tabelle SALES in Diagramm 1 an.
PRODUCT YEAR AMOUNT ------- ---------- ---------- paper 2006 2 pen 2006 80 staples 2006 18 paper 2007 4 pen 2007 100 staples 2007 30
Angenommen du willst die Verkäufe für 2008 berechnen , indem du die Verkäufe aus 2007 mit demselben Wachstumsfaktor multiplizierst wie von 2006 auf 2007. In einer Formel:
sales[2008] = sales[2007] * ( sales[2007] / sales[2006] )
Mit Hilfe der Model Clause kann die Frage so beantwortet werden:
SQL> select product 2 , year 3 , amount 4 from sales 5 model 6 partition by (product) 7 dimension by (year) 8 measures (amount) 9 rules 10 ( amount[2008] = amount[2007] * amount[2007] / amount[2006] 11 ) 12 order by year 13 , product 14 / PRODUCT YEAR AMOUNT ------- ---------- ---------- paper 2006 2 pen 2006 80 staples 2006 18 paper 2007 4 pen 2007 100 staples 2007 30 paper 2008 8 pen 2008 125 staples 2008 50 9 Zeilen ausgewählt.
Es ist nicht unbedingt notwendig die Model Clause für eine derartige Abfrage zu verwenden, du kannst das gleiche Ergebnis mit einem UNION ALL erreichen und die Verkäufe des nächsten Jahres in einer separaten Abfrage berechnen; oder du verwendest Grouping Sets. Aber dann werden diese Statements schnell unübersichtlich und kompliziert, und was, wenn du auch noch die Prognose für 2009 willst, z.B. indem du die Verkäufe von 2008 mit 2 multiplizierst? Mit der Model Clause fügst du nur eine weitere Regel hinzu:
amount[2009] = amount[2008] * 2
Vorhersageabfragen sind also deshalb die Beispiele in Kapitel 22 des Data Warehousing Guide, weil die SQL Model Clause sie ohne großes zutun berechnen kann.
Zeilengenerierung
Über viele Jahre haben wir Zeilen generiert, indem wir aus einer Tabelle oder View selektiert haben, die mindestens die Anzahl Zeilen enthielt, die wir generieren wollten. Üblicherweise wurde die View all_objects folgendermaßen verwendet:
select rownum from all_objects where rownum <= 100000
Dann wartete Mikito Harakiri mit dieser simplen Zeilengenerierungsabfrage gegen dual auf:
select level from dual connect by level <= 100000
Mit der Model Clause gibt es noch eine Alternative:
select i
from dual
model
dimension by (1 i)
measures (0 x)
(x[for i from 2 to 100000 increment 1] = 0)
Und hier ist ein Auszug aus einem tkprof eines 10046 level 8 trace der drei Varianten beim generieren von 100.000 Zeilen:
select rownum
from all_objects
where rownum <= 100000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.24 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4564 9.90 12.50 557 179849 0 68433
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4566 10.04 12.75 557 179849 0 68433
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
68433 COUNT STOPKEY (cr=186696 pr=636 pw=0 time=61522 us)
68433 FILTER (cr=186696 pr=636 pw=0 time=61522 us)
70174 HASH JOIN (cr=5383 pr=0 pw=0 time=0 us cost=44 size=1198062 card=11862)
91 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=87 card=29)(object id 47)
70174 HASH JOIN (cr=5382 pr=0 pw=0 time=0 us cost=42 size=1162476 card=11862)
91 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=638 card=29)(object id 47)
70174 TABLE ACCESS FULL OBJ$ (cr=5381 pr=0 pw=0 time=0 us cost=41 size=901512 card=11862)
3309 TABLE ACCESS BY INDEX ROWID IND$ (cr=1616 pr=5 pw=0 time=0 us cost=2 size=8 card=1)
4103 INDEX UNIQUE SCAN I_IND1 (cr=601 pr=4 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
25305 HASH JOIN (cr=43900 pr=98 pw=0 time=0 us cost=3 size=24 card=1)
25702 INDEX RANGE SCAN I_OBJAUTH1 (cr=43900 pr=98 pw=0 time=0 us cost=2 size=11 card=1)(object id 62)
28897 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
1 FIXED TABLE FULL X$KZSPR (cr=8 pr=1 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 NESTED LOOPS (cr=88452 pr=386 pw=0 time=0 us)
0 NESTED LOOPS (cr=88452 pr=386 pw=0 time=0 us cost=6 size=70 card=1)
0 NESTED LOOPS (cr=88452 pr=386 pw=0 time=0 us cost=4 size=60 card=1)
49740 NESTED LOOPS (cr=83309 pr=386 pw=0 time=0 us cost=3 size=49 card=1)
49740 MERGE JOIN CARTESIAN (cr=80820 pr=386 pw=0 time=0 us cost=2 size=46 card=1)
2487 INDEX RANGE SCAN I_OBJ5 (cr=80820 pr=386 pw=0 time=0 us cost=2 size=33 card=1)(object id 40)
49740 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
49740 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
49740 INDEX RANGE SCAN I_USER2 (cr=2489 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=5143 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)
0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=3)(object id 106)
0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=10 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
23864 HASH JOIN (cr=28167 pr=0 pw=0 time=0 us cost=3 size=24 card=1)
24116 INDEX RANGE SCAN I_OBJAUTH1 (cr=28167 pr=0 pw=0 time=0 us cost=2 size=11 card=1)(object id 62)
26105 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
99 NESTED LOOPS (cr=1761 pr=0 pw=0 time=0 us cost=2 size=48 card=2)
120 INDEX RANGE SCAN I_OBJAUTH1 (cr=1761 pr=0 pw=0 time=0 us cost=2 size=11 card=1)(object id 62)
99 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
0 NESTED LOOPS (cr=7012 pr=68 pw=0 time=0 us cost=6 size=70 card=1)
4820 MERGE JOIN CARTESIAN (cr=6454 pr=68 pw=0 time=0 us cost=5 size=59 card=1)
241 NESTED LOOPS (cr=6454 pr=68 pw=0 time=0 us)
1458 NESTED LOOPS (cr=6205 pr=28 pw=0 time=0 us cost=5 size=46 card=1)
249 NESTED LOOPS (cr=5684 pr=0 pw=0 time=0 us cost=3 size=36 card=1)
249 INDEX RANGE SCAN I_OBJ5 (cr=5443 pr=0 pw=0 time=0 us cost=2 size=33 card=1)(object id 40)
249 INDEX RANGE SCAN I_USER2 (cr=241 pr=0 pw=0 time=0 us cost=1 size=3 card=1)(object id 47)
1458 INDEX RANGE SCAN I_DEPENDENCY1 (cr=521 pr=28 pw=0 time=0 us cost=1 size=0 card=3)(object id 106)
241 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=249 pr=40 pw=0 time=0 us cost=2 size=10 card=1)
4820 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=3 size=1300 card=100)
4820 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=1300 card=100)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=558 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
4 NESTED LOOPS (cr=2498 pr=0 pw=0 time=0 us cost=2 size=72 card=2)
10 NESTED LOOPS (cr=2498 pr=0 pw=0 time=0 us cost=2 size=23 card=1)
461 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1967 pr=0 pw=0 time=0 us cost=1 size=12 card=1)
488 INDEX UNIQUE SCAN I_TRIGGER2 (cr=1479 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 160)
10 INDEX RANGE SCAN I_OBJAUTH1 (cr=531 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)
4 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
92 VIEW (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
92 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
2 NESTED LOOPS (cr=1060 pr=0 pw=0 time=0 us cost=2 size=42 card=2)
4 INDEX RANGE SCAN I_OBJAUTH1 (cr=1060 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
2 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=42 card=2)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)
0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)
0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
0 VIEW (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=1 size=86 card=1)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=1 size=86 card=1)
0 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us cost=0 size=78 card=1)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=65 card=1)
0 INDEX UNIQUE SCAN I_OLAP_CUBES$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 899)
0 TABLE ACCESS BY INDEX ROWID OLAP_DIMENSIONALITY$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
0 INDEX RANGE SCAN I_OLAP_DIMENSIONALITY$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 903)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
0 INDEX FULL SCAN I_OLAP_CUBE_DIMENSIONS$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 887)
0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)(object id 36)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
0 INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)(object id 39)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4564 0.00 0.02
SQL*Net message from client 4564 0.00 0.80
db file sequential read 557 0.07 2.36
********************************************************************************
select level
from dual
connect by level <= 100000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 0.31 0.36 0 0 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 0.31 0.36 0 0 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
100000 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6668 0.00 0.02
SQL*Net message from client 6668 0.00 0.75
********************************************************************************
select i
from dual
model
dimension by (1 i)
measures (0 x)
(x[for i from 2 to 100000 increment 1] = 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 3.39 3.19 0 0 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 3.39 3.19 0 0 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
100000 SQL MODEL ORDERED (cr=0 pr=0 pw=0 time=0 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6668 0.00 0.02
SQL*Net message from client 6668 0.00 0.75
********************************************************************************
Die Model Clause ist schneller als die gute alte Abfrage auf all_objects, aber ciel langsamer als die "connect by level" Abfrage. Es ist daher nicht wert die SQL Model Clause für reines Generieren von Zeilen zu verwenden. Nichtsdestotrotz, für abgeleitete Probleme der Zeilengenerierung kann die Model Clause eine Option sein. Ein Beispiel für ein derartiges Problem ist das Aufteilen von Perioden in einzelne Tage in einem Kalender, wenn z.B. eine Zeile das Startdatum 1. Januar und das Endedatum 5. Januar enthält und in 5 einzelne Zeilen aufgeteilt werden muss. Ein anders Beispiel ist das Aufteilen einer Zeichenkette in einzelne Wörter:
SQL> create table t (id,str) 2 as 3 select 1, 'OGh Visie' from dual union all 4 select 2, 'Oracle Gebruikersclub Holland' from dual union all 5 select 3, null from dual union all 6 select 4, 'OGh' from dual 7 / Tabelle wurde erstellt.
Um die einzelnen Wörter zu extrahieren kannst du eine der beiden folgenden Abfragen verwenden:
SQL> select id
2 , i seqnr
3 , str word
4 from t
5 model
6 return updated rows
7 partition by (id)
8 dimension by (0 i)
9 measures (str)
10 ( str[for i from 1 to regexp_count(str[0],' ')+1 increment 1]
11 = regexp_substr(str[0],'[^ ]+',1,cv(i))
12 )
13 order by id
14 , seqnr
15 /
ID SEQNR WORD
---------- ---------- -----------------------------
1 1 OGh
1 2 Visie
2 1 Oracle
2 2 Gebruikersclub
2 3 Holland
4 1 OGh
6 Zeilen ausgewählt.
SQL> select id
2 , n seqnr
3 , regexp_substr(str,'[^ ]+',1,n) word
4 from t
5 , ( select level n
6 from ( select max(regexp_count(str,' '))+1 max_#words
7 from t
8 )
9 connect by level <= max_#words
10 )
11 where n <= regexp_count(str,' ')+1
12 order by id
13 , seqnr
14 /
ID SEQNR WORD
---------- ---------- -----------------------
1 1 OGh
1 2 Visie
2 1 Oracle
2 2 Gebruikersclub
2 3 Holland
4 1 OGh
6 Zeilen ausgewählt.
Du kannst sehen, wie sie auf den vorher beschriebenen reinen Zeilengenerierungsabfragen basieren. In diesen Lösungen berechnet "regexp_count(str,' ')+1" die Anzahl der Wörter indem es die Anzahl der Leerzeichen sucht und 1 hinzuzählt. "regexp_substr(str,'[^ ]+',1,n)" liefert das n-te Wort aus dem String.
Besonders für die "connect by level" Abfrage sind mehrere Varianten möglich. Alle sehen ziemlich kryptisch aus, was sie schwer wartbar macht. Die SQL Model Clause behält die Lesbarkeit durch die Verwendung der Partition Clause.
Mit einer 10.000-fach vergrößerten Tabelle t sieht der tkprof folgendermaßen aus:
select id
, i seqnr
, str word
from t
model
return updated rows
partition by (id)
dimension by (0 i)
measures (str)
( str[for i from 1 to regexp_count(str[0],' ')+1 increment 1]
= regexp_substr(str[0],'[^ ]+',1,cv(i))
)
order by id
, seqnr
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4001 1.56 1.58 0 142 0 60000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4003 1.56 1.58 0 142 0 60000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
60000 SORT ORDER BY (cr=142 pr=0 pw=0 time=0 us cost=286 size=640000 card=40000)
60000 SQL MODEL ORDERED (cr=142 pr=0 pw=0 time=0 us cost=286 size=640000 card=40000)
40000 TABLE ACCESS FULL T (cr=142 pr=0 pw=0 time=0 us cost=69 size=640000 card=40000)
********************************************************************************
select id
, n seqnr
, regexp_substr(str,'[^ ]+',1,n) word
from t
, ( select level n
from ( select max(regexp_count(str,' '))+1 max_#words
from t
)
connect by level <= max_#words
)
where n <= regexp_count(str,' ')+1
order by id
, seqnr
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4001 1.32 1.29 0 568 0 60000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4003 1.32 1.29 0 568 0 60000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 88
Rows Row Source Operation
------- ---------------------------------------------------
60000 SORT ORDER BY (cr=568 pr=0 pw=0 time=0 us cost=140 size=58000 card=2000)
60000 NESTED LOOPS (cr=568 pr=0 pw=0 time=20508 us cost=139 size=58000 card=2000)
3 VIEW (cr=142 pr=0 pw=0 time=0 us cost=69 size=13 card=1)
3 CONNECT BY WITHOUT FILTERING (cr=142 pr=0 pw=0 time=0 us)
1 VIEW (cr=142 pr=0 pw=0 time=0 us cost=69 size=13 card=1)
1 SORT AGGREGATE (cr=142 pr=0 pw=0 time=0 us)
40000 TABLE ACCESS FULL T (cr=142 pr=0 pw=0 time=0 us cost=69 size=480000 card=40000)
60000 TABLE ACCESS FULL T (cr=426 pr=0 pw=0 time=20508 us cost=69 size=32000 card=2000)
********************************************************************************
Wieder gewinnt der "connect by level" Trick, aber diesmal ist es viel knapper. Deshalb könntest du hier die Lesbarkeit überwiegen lassen.
Berechnungen basierend auf berechneten Werten
Es gibt Fragen dort draußen, die scheinen mit purem SQL unmöglich lösbar, für die die Model Clause eine elegante Lösung bietet. Das ist eine Klasse von Problemen, bei denen Berechnungen wiederholt auf berechneten Werten durchgeführt werden müssen. Ein Beispiel sagt mehr als tausend Worte, hier ist also eins:
SQL> select * from deposits
2 /
CUSTOMER AMOUNT DEPOSIT_DATE
---------- ---------- --------------
1 1000 januar 2003
1 200 januar 2004
1 500 januar 2005
1 100 januar 2006
1 800 januar 2007
2 20 januar 2003
2 150 januar 2004
2 60 januar 2005
2 100 januar 2006
2 100 januar 2007
10 Zeilen ausgewählt.
SQL> select * from interest_rates
2 /
STARTDATE PERCENTAGE
-------------- ----------
januar 2003 5
januar 2004 3,2
januar 2005 4,1
januar 2006 5,8
januar 2007 4,9
5 Zeilen ausgewählt.
Die Frage ist: „Wie sieht das Saldo am Ende des Jahres aus?“. Für den Kunden 1 ist es am Ende 2003: 100 * 1,05 = 1050. Ende 2004 ist es (1050 + 200) * 1,032 = 1290, etc. Das ist es, was ich mit Berechnungen meine, die auf berechneten Werten basieren, und die unbestimmte Male durchgeführt werden müssen. Mit Hilfe der Model Clause wird diese Abfrage relativ einfach, etwa so:
SQL> select customer
2 , amount
3 , deposit_date
4 , percentage
5 , balance balance_at_end_of_year
6 from deposits s
7 , interest_rates r
8 where s.deposit_date = r.startdate
9 model
10 partition by (s.customer)
11 dimension by (s.deposit_date)
12 measures (s.amount, r.percentage, 0 balance)
13 rules
14 ( balance[any] order by deposit_date
15 = round
16 ( (nvl(balance[add_months(cv(),-12)],0) + amount[cv()])
17 * (1 + percentage[cv()]/100)
18 , 2
19 )
20 )
21 order by customer
22 , deposit_date
23 /
CUSTOMER AMOUNT DEPOSIT_DATE PERCENTAGE BALANCE_AT_END_OF_YEAR
---------- ---------- -------------- ---------- ----------------------
1 1000 januar 2003 5 1050
1 200 januar 2004 3,2 1290
1 500 januar 2005 4,1 1863,39
1 100 januar 2006 5,8 2077,27
1 800 januar 2007 4,9 3018,26
2 20 januar 2003 5 21
2 150 januar 2004 3,2 176,47
2 60 januar 2005 4,1 246,17
2 100 januar 2006 5,8 366,25
2 100 januar 2007 4,9 489,1
10 Zeilen ausgewählt.
Für diesen Typ von Berechnungen gibt es in SQL schwerlich eine Alternative, abgesehen von einer sehr langsamen, fortgeschrittenen XML Funktion. Vor Oracle 10 hättest du vielleicht auf eine prozedurale Sprache wie PL/SQL zurückgegriffen. Das brauchst du heutzutage nicht mehr.
Komplexe Algorithmen
Mit der SQL Model Clause ist es möglich geworden Algorithmen umzusetzen, die vorher in SQL nicht möglich waren, selbst einige der komplexesten. Ein Beispiel ist eine Abfrage um Tablespaces gleichmäßig so über Dateien einer Datenbank zu verteilen, dass der freie Speicher so gleich wie möglich verteilt ist. Oder sogar eine einzelne Abfrage um jedes Sudoku Puzzle zu lösen, wenn ein String mit 81 Zahlen vorgegeben. Auch wenn es ein großer Spaß sein kann diese zu schreiben, wirst du zugeben müssen, dass es ein Albtraum ist sie zu warten, wenn du nicht der ursprüngliche Autor bist. Abgesehen von der geringeren Zeilenzahl und einem kleinen Performancevorteil scheint eine (pipelined) PL/SQL Funktion hier besser angebracht zu sein.
Zusammenfassen von Zeichenketten (String Aggregation)
String Aggregation ist eigentlich nur ein Spezialfall der Kategorie "Berechnungen basierend auf berechneten Werten". Aber es ist eine so häufig gestellte Frage in den Oracle Foren, dass ich sie separat behandeln will.
String Aggregation ist der Vorgang des Zusammenfassens verschiedener Zeilen in eine Zeile und dem “Verknüpfen” aller Spaltenwerte, möglicherweise getrennt durch ein spezielles Zeilen, etwa ein Komma. Ein Beispiel mit der bekannten EMP Tabelle soll folgendes Ergebnis liefern:
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
Es gibt viele Wege zur String Aggregation:
- über eine benutzerdefinierte Aggregatsfunktion, zum Beispiel Tom Kyte’s stragg Funktion,
- eine hierarchische Abfrage mit sys_connect_by_path
- mit XML-Functionen
- über die COLLECT Funktion, einen SQL Typund eine benutzerdefinierte Funktion
- über die undokumentierte (und daher nicht empfohlene) wmsys.wm_concat
Mit der Model Clause wird es folgendermaßen gemacht:
SQL> select deptno
2 , rtrim(ename,',') enames
3 from ( select deptno
4 , ename
5 , rn
6 from emp
7 model
8 partition by (deptno)
9 dimension by (row_number() over
10 (partition by deptno order by ename) rn
11 )
12 measures (cast(ename as varchar2(40)) ename)
13 rules
14 ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
15 )
16 )
17 where rn = 1
18 order by deptno
19 /
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,JONES
30 ALLEN,MARTIN,TURNER,WARD
3 Zeilen ausgewählt.
Nur eine Abfrage, keine Hilfsobjekte und schneller als alle anderen vorher erwähnten Alternativen. Es gibt nichts umsonst: Der Preis ist ein höherer PGA Speicherverbrauch. Im Allgemeinen ist das kein Problem, falls genügend Speicher vorhanden ist. Falls nicht, wirst du Wait Events wie 'direct path read temp' und 'direct path write temp' bemerken und der Performancegewinn ändert sich schnell in einen Performanceverlust.
Zusammenfassung
Für eine Reihe von Problemen ist die SQL Model Clause eine nützliche Erweiterung der SQL Sprache. Dies gilt vor allem für Vorhersageabfragen und Berechnungen, die auf berechneten Werten basieren, einschließlich der String Aggregation. Die Model Clause kann auch für komplexe Algorithmen verwendet werden und zur Zeilengenerierung. Aber dies bedeutet Einschränkungen bei der Wartbarkeit im ersten Fall und bei der Performance im zweiten. Nichtsdestotrotz, wenn du die Möglichkeiten von SQL ausnützen willst, dann ist das Lernen und Verwenden der Model Clause den Aufwand in jedem Fall wert.
oracle@matzberger.de Für Kontakte aller Art, auch Fehlermeldungen zum Tutorial
Impressum