Dit artikel gaat in op een aantal vaker voorkomende soorten zoekvragen en zet daarbij een oplossing met de modelclausule af tegen het beste alternatief, als deze bestaat...

Welk soort problemen zijn perfect met de modelclausule op te lossen en welke niet

De SQL modelclausule in de praktijk

Door Rob van Wijk

Als je beroepshalve SQL zoekvragen ontwikkelt, dan is het de moeite waard om kennis te nemen van de SQL modelclausule. Wanneer eenmaal de angst voor de nieuwe syntax is overwonnen en de basisprincipes eigen zijn gemaakt, dan zul je ontdekken dat je minder vaak dan voorheen op een procedurele taal zult terugvallen. Dit zal over het algemeen resulteren in kortere, duidelijkere en vaak snellere code. Dit artikel gaat in op een aantal vaker voorkomende soorten zoekvragen en zet daarbij een oplossing met de modelclausule af tegen het beste alternatief, als deze bestaat. Hierbij zal gekeken worden naar leesbaarheid en snelheid. Uiteindelijk zul je gemakkelijker situaties gaan herkennen waarin de modelclausule kan of zou moeten worden ingezet.

Achtergrond

De modelclausule werd ge‹ntroduceerd in versie 10g Release 1 in 2003. Volgens de documentatie kun je er vanuit de resultaten van een zoekvraag multidimensionale arrays mee vormen en daarop formules loslaten om nieuwe waarden te berekenen. Ze kunnen daarmee spreadsheet-applicaties op de PC vervangen. Echter, afgaande op eigen ervaring en op de Oracle forums, wordt de SQL modelclausule nog weinig gebruikt. En als de modelclausule gebruikt wordt, dan is het niet om spreadsheet-applicaties op de PC te vervangen. Het type problemen waarvoor de modelclausule wel gebruikt kan worden en die hier behandeld zullen worden, zijn:

  • voorspellingen/begrotingen
  • rijgeneratie
  • variabel aantal berekeningen gebaseerd op berekende waarden
  • complexe algoritmen
  • stringaggregatie

Natuurlijk beperkt het gebruik van de modelclausule zich niet tot deze opsomming, maar bovenstaande vijf ben ik het meest tegengekomen.

Wie nog niet bekend is met de SQL modelclausule kan de schade inhalen door Hoofdstuk 22 van de Data Warehousing Guide[1] te lezen. Ook de SQL Model Clause Tutorial, part one[2] and part two[3] op mijn weblog kan ik uiteraard aanbevelen.

Voorspellingen/begrotingen

Dit type zoekvraag wordt bijna uitsluitend gebruikt in het hierboven genoemde hoofdstuk 22 van de Data Warehousing Guide. Typisch wordt er hier een tabel of view met aantallen verkopen genomen, waarbij de te beantwoorden vraag er een is die moet voorspellen hoe hoog de toekomstige verkoopaantallen zullen zijn op basis van een of meer rekenregels. Neem bijvoorbeeld de volgende inhoud van de tabel VERKOPEN:

PRODUCT JAAR AANTAL
------- ---------- ----------
nietjes 2006 18
papier 2006 2
pennen 2006 80
nietjes 2007 30
papier 2007 4
pennen 2007 100

De vraag is om de verwachte verkoopaantallen voor 2008 te tonen op basis van de regel dat de verkoopaantallen voor 2008 gelijk zijn aan de verkoopaantallen in 2007, vermenigvuldigd met hetzelfde groeipercentage als van 2006 op 2007. In formulevorm (per product):

verkopen2008 = verkopen2007 * (verkopen2007 / verkopen2006)

Met de modelclausule wordt dit als volgt opgelost:

SQL> select product
2 , jaar
3 , aantal
4 from verkopen
5 model
6 partition by (product)
7 dimension by (jaar)
8 measures (aantal)
9 rules
10 ( aantal[2008] = aantal[2007] * aantal[2007] / aantal[2006]
11 )
12 order by jaar
13 , product
14 /
PRODUCT JAAR AANTAL
------- ---------- ----------
papier 2006 2
pennen 2006 80
nietjes 2006 18
papier 2007 4
pennen 2007 100
nietjes 2007 30
papier 2008 8
pennen 2008 125
nietjes 2008 50

Ook zonder de modelclausule te gebruiken, kan dit resultaat worden bereikt. Bijvoorbeeld door inzet van een UNION ALL en een extra rondgang door de tabel, of met behulp van grouping sets. Echter, als je dit uitschrijft, blijkt snel dat het behoorlijk gekunsteld is en lastig leest. En wat als je ook een prognose van de verkoopaantallen van 2009 wilt van bijvoorbeeld tweemaal die van 2008? Met de modelclausule voeg je gewoon een extra regel toe:

aantal[2009] = aantal[2008] * 2

Het is dus niet voor niets dat voorspellingen en begrotingen als h‚t voorbeeld in de Oracle documentatie worden genomen.

Rijgeneratie

Sinds 2003 is bekend dat je gemakkelijk en snel rijen kunt genereren door gebruik te maken van een hi‰rarchische zoekvraag op dual:

select level
from dual
connect by level <= 100000
Met de modelclausule heb je echter nog een alternatief:
select i
from dual
model
dimension by (1 i)
measures (0 x)
(x[for i from 2 to 100000 increment 1] = 0)
De totalen in het tkprof-bestand laat het volgende zien, in dezelfde volgorde als hierboven:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ------- -----------
total 6670 0.39 0.36 0 0 0 100000
total 6670 3.01 3.13 0 0 0 100000

De modelclausule is dus ruim langzamer dan de zoekvraag met connect-by-level en is dus niet interessant voor pure rijgeneratie. Voor afgeleide zoekvragen kan de modelclausule echter wel interessant zijn. Denk bij afgeleide zoekvragen bijvoorbeeld aan het uitvouwen van periodes in een agenda, waarbij je van ‚‚n rij met begindatum 1-1-2008 en einddatum 5-1-2008, 5 rijen moet maken. Of denk aan het splitsen van strings in individuele elementen. Zie het volgende voorbeeld:

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 /

Om hier de individuele woorden uit te selecteren, waarbij uiteraard de spatie het scheidingsteken is, kan je ‚‚n van de volgende twee varianten gebruiken:

SQL> select id
2 , n volgnr
3 , regexp_substr(str,'[^ ]+',1,n) woord
4 from t
5 , ( select level n
6 from ( select max(regexp_count(str,' '))+1 max_#woorden
7 from t
8 )
9 connect by level <= max_#woorden
10 )
11 where n <= regexp_count(str,' ')+1
12 order by id
13 , volgnr
14 /
ID VOLGNR WOORD
------ ---------- -----------------------------
1 1 OGh
1 2 Visie
2 1 Oracle
2 2 Gebruikersclub
2 3 Holland
4 1 OGh
6 rijen zijn geselecteerd.
SQL> select id
2 , i volgnr
3 , str woord
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 , volgnr
15 /
ID VOLGNR WOORD
------- ---------- -----------------------------
1 1 OGh
1 2 Visie
2 1 Oracle
2 2 Gebruikersclub
2 3 Holland
4 1 OGh
6 rijen zijn geselecteerd.

Je ziet in beide zoekvragen de basiszoekvraag terug. In deze oplossingen rekent regexp_count(str,’ ‘)+1 het aantal woorden in de string uit (vereist minimaal versie 11.1.0.6), en haalt regexp_substr(str,’[^ ]+’,1,n) het n-de woord uit de string. Voor de eerste zoekvraag zijn diverse alternatieven gebaseerd op de connect-by-level truc bekend. Ze ogen echter allemaal nogal cryptisch, wat ze lastiger onderhoudbaar maakt. De modelclausule daarentegen behoudt zijn leesbaarheid vanwege de partitieclausule. Met een 10.000 maal vergrootte tabel t, is het volgende te zien in een tkprof-bestand:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- -----------------
total 4004 1.92 1.88 0 571 0 60006
total 4004 2.18 2.20 0 143 0 60006
...

Qua snelheid moet de modelclausule het hier dus afleggen. Ook de eerder genoemde alternatieven blijken iets sneller. Leesbaarheid is echter ook iets waard.

Variabel aantal berekeningen gebaseerd op berekende waarden

Een voorbeeld zegt meer dan duizend woorden, dus stel jezelf een sterk versimpeld financieel product voor waarbij de klant gedurende een aantal jaren op 1 januari een storting doet en waarbij hij over dat gehele jaar een rentevergoeding krijgt. De tabellen voor deze situatie zijn:

SQL> create table stortingen (klantnummer, bedrag, datum)
2 as
3 select 1, 1000, date '2003-01-01' from dual union all
4 select 1, 200, date '2004-01-01' from dual union all
5 select 1, 800, date '2007-01-01' from dual union all
6 select 1, 500, date '2005-01-01' from dual union all
7 select 1, 100, date '2006-01-01' from dual union all
8 select 2, 20, date '2003-01-01' from dual union all
9 select 2, 150, date '2004-01-01' from dual union all
10 select 2, 60, date '2005-01-01' from dual union all
11 select 2, 100, date '2006-01-01' from dual union all
12 select 2, 100, date '2007-01-01' from dual
13 /
Tabel is aangemaakt.
SQL> create table rentes (datum, percentage)
2 as
3 select date '2003-01-01', 5 from dual union all
4 select date '2004-01-01', 3.2 from dual union all
5 select date '2005-01-01', 4.1 from dual union all
6 select date '2006-01-01', 5.8 from dual union all
7 select date '2007-01-01', 4.9 from dual
8 /
Tabel is aangemaakt.

De vraag is dan wat het saldo aan het eind van ieder jaar is. Voor klant 1 is dit aan het eind van 2003: 1000 * 1,05 = 1050. Aan het eind van 2004 is dit (1050 + 200) * 1,032 = 1290, enzovoorts. Dit is dus wat ik bedoel met berekeningen over berekende waarden. Met de modelclausule wordt de zoekvraag zo:

SQL> select klantnummer
2 , bedrag
3 , datum
4 , percentage
5 , saldo saldo_aan_eind_van_jaar
6 from stortingen s
7 , rentes r
8 where s.datum = r.datum
9 model
10 partition by (s.klantnummer)
11 dimension by (s.datum)
12 measures (s.bedrag, r.percentage, 0 saldo)
13 rules
14 ( saldo[any] order by datum
15 = round
16 ( (nvl(saldo[add_months(cv(),-12)],0) + bedrag[cv()])
17 * (1 + percentage[cv()]/100)
18 , 2
19 )
20 )
21 order by klantnummer
22 , datum
23 /
KLANTNUMMER BEDRAG DATUM PERCENTAGE SALDO_AAN_EIND_VAN_JAAR
----------- ------ ---------- ---------- -----------------------
1 1000 01-01-2003 5 1050
1 200 01-01-2004 3,2 1290
1 500 01-01-2005 4,1 1863,39
1 100 01-01-2006 5,8 2077,27
1 800 01-01-2007 4,9 3018,26
2 20 01-01-2003 5 21
2 150 01-01-2004 3,2 176,47
2 60 01-01-2005 4,1 246,17
2 100 01-01-2006 5,8 366,25
2 100 01-01-2007 4,9 489,1

Als je de berekende waarden wilt kunnen afronden, dan is er nauwelijks een alternatief om dit voor elkaar te krijgen, behalve een erg langzame met geavanceerde XML-functies. Voor versie 10g zou je zo’n vraagstuk waarschijnlijk met een procedurele taal als PL/SQL oplossen, maar dat hoeft nu dus niet meer.

Complexe algoritmes

Met de modelclausule worden de grenzen van wat mogelijk is in SQL flink opgerekt. Ook complexe algoritmes kun je ermee oplossen. Een voorbeeld hiervan is een zoekvraag om tabelruimtes over de databestanden van een database dusdanig te verdelen dat de vrije ruimte zo gelijk mogelijk blijft[4]. Of zelfs een enkele zoekvraag om iedere sudoku-puzzel te kunnen oplossen als je hem een string van 81 cijfers voert[5]. Als je ervan houdt, is dit hartstikke leuk om te schrijven, maar je hebt wel een probleem als je niet de auteur bent en je er onderhoud op moet uitvoeren. Dus ongeacht een eventueel gereduceerde aantal regels code of kleine snelheidswinsten, lijken pipelined functies hier altijd een beter alternatief.

Stringaggregatie

Stringaggregatie is eigenlijk een speciaal geval van de categorie ‘variabel aantal berekeningen over berekende waarden’. De vraag wordt echter zo vaak gesteld op Oracle forums dat ik hem hier apart noem. Met stringaggregatie wordt bedoeld dat meerdere rijen worden geaggregeerd tot ‚‚n rij, waarbij de waarden van een kolom achter elkaar worden gezet, gescheiden door bijvoorbeeld een komma. Een voorbeeld met de bekende EMP-tabel is het produceren van de volgende uitvoer:

DEPTNO ENAMES
---------- --------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Je kunt dit op vele manieren doen:

  • met een eigen aggregatiefunctie, bijvoorbeeld Tom Kyte’s stragg functie
  • met een hierarchische zoekvraag met sys_connect_by_path
  • met XML-functiesmet de COLLECT functie, een SQL type en een eigen gemaakte functiemet het ongedocumenteerde - en daarom afgeraden - wmsys.wm_concat

Met de modelclausule los je het zo op:

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
15 = ename[cv()]||','||ename[cv()+1]
16 )
17 )
18 where rn = 1
19 order by deptno
20 /

E‚n zoekvraag, geen hulpobjecten en sneller dan alle andere alternatieven[6]. De prijs die je ervoor betaalt is een groter beroep op PGA-geheugen, wat vaak geen probleem is, mits er voldoende aanwezig is. Is dat niet het geval, dan zie je ‘direct path read temp’ en ‘direct path write temp’ bij je wachttijden staan en kan de kleine snelheidswinst omslaan in een groot snelheidsverlies.

Conclusie

De SQL modelclausule is voor een aantal soort zoekvragen een echte aanwinst. Met name bij voorspellingen en vraagstukken waarbij je een variabel aantal berekeningen over berekende waarden wilt uitvoeren, inclusief stringaggregatie, blinkt de modelclausule uit. Complexere algoritmes en zoekvragen waarbij je rijen genereert ‘uit het niets’, kun je er ook mee oplossen. In het eerste geval gaat dat echter vaak ten koste van de onderhoudbaarheid. In het tweede geval, blijkt dat qua snelheid niets opgewassen is tegen het genereren van rijen met de connect-by-level truc. Desondanks is er mijns inziens genoeg reden om de SQL modelclausule verder te bestuderen en te gebruiken.

Rob van Wijk is werkzaam bij CIBER Nederland.

[1] http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1855

[2] http://rwijk.blogspot.com/2007/10/sql-model-clause-tutorial-part-one.html

[3] http://rwijk.blogspot.com/2007/10/sql-model-clause-tutorial-part-two.html

[4] http://forums.oracle.com/forums/message.jspa?messageID=2044529#2044529

[5] http://technology.amis.nl/blog/?p=2066

[6] http://forums.oracle.com/forums/message.jspa?messageID=1819487#1819487