Monday, March 9, 2009

Calculating probabilities with N throws of a die

A translation of this post in German can be found here.


Chen Shapira pointed me to a SQL riddle by Iggy Fernandez. Laurent Schneider already gave an impressively simple solution using a hierarchical query and the XMLQuery function. I used the SQL model clause (of course) to achieve the same:

rwijk@ORA11GR1> select * from die order by face_id
2 /

FACE_ID FACE_VALUE PROBABILITY
---------- ---------- -----------
1 1 .25
2 3 .25
3 4 .25
4 5 .083333333
5 6 .083333333
6 8 .083333333

6 rows selected.

rwijk@ORA11GR1> var N number
rwijk@ORA11GR1> exec :N := 2

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
2 .0625
4 .125
5 .125
6 .104166667
7 .166666667
8 .104166667
9 .125
10 .048611111
11 .055555556
12 .048611111
13 .013888889
14 .013888889
16 .006944444
1

14 rows selected.

rwijk@ORA11GR1> exec :N := 3

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
3 .015625
5 .046875
6 .046875
7 .0625
8 .109375
9 .09375
10 .125
11 .098958333
12 .104166667
13 .088541667
14 .057291667
15 .05787037
16 .032986111
17 .027777778
18 .012731481
19 .008680556
20 .006944444
21 .001736111
22 .001736111
24 .000578704
1

21 rows selected.


And with a traditional die:

rwijk@ORA11GR1> update die
2 set face_value = face_id
3 , probability = 1/6
4 /

6 rows updated.

rwijk@ORA11GR1> select * from die order by face_id
2 /

FACE_ID FACE_VALUE PROBABILITY
---------- ---------- -----------
1 1 .166666667
2 2 .166666667
3 3 .166666667
4 4 .166666667
5 5 .166666667
6 6 .166666667

6 rows selected.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
3 .00462963
4 .013888889
5 .027777778
6 .046296296
7 .069444444
8 .097222222
9 .115740741
10 .125
11 .125
12 .115740741
13 .097222222
14 .069444444
15 .046296296
16 .027777778
17 .013888889
18 .00462963
1

17 rows selected.

rwijk@ORA11GR1> exec :N := 4

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> with number_of_dies as (select count(*) cnt from die)
2 , all_probabilities as
3 ( select sum_value
4 , prob
5 , i
6 from (select level l from number_of_dies connect by level <= power(cnt,:N))
7 , number_of_dies
8 model
9 reference r on (select face_id, face_value, probability from die)
10 dimension by (face_id)
11 measures (face_value,probability)
12 main m
13 partition by (l rn, cnt)
14 dimension by (0 i)
15 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
16 rules iterate (1000) until (iteration_number + 1 = :N)
17 ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
18 , remainder[0] = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
19 , sum_value[0] = sum_value[0] + face_value[die_face_id[0]]
20 , prob[0] = prob[0] * probability[die_face_id[0]]
21 )
22 )
23 select sum_value "Sum"
24 , sum(prob) "Probability"
25 from all_probabilities
26 group by rollup(sum_value)
27 order by sum_value
28 /

Sum Probability
---------- -----------
4 .000771605
5 .00308642
6 .007716049
7 .015432099
8 .027006173
9 .043209877
10 .061728395
11 .080246914
12 .096450617
13 .108024691
14 .112654321
15 .108024691
16 .096450617
17 .080246914
18 .061728395
19 .043209877
20 .027006173
21 .015432099
22 .007716049
23 .00308642
24 .000771605
1

22 rows selected.


About the solution: it generates as much rows as there are combinations (power(cnt,:N)). With the die_face_id and remainder measures, each generated row is converted to N face_id's in N iterations. The face_value and probability numbers are looked up using a reference model. Finally, the outer query just sums up all probabilities.

The SQL language is "not complete", but since version 10 I seriously wonder if there are problems out there that cannot be solved using SQL. Although it may look like a pretzel to some, with some comments I'm sure it's not that hard to maintain.

Sunday, March 1, 2009

Pulling XML out of the database

To have an XMLType variable in your PL/SQL program, and filled with database content, I almost exclusively use the XML publishing functions like XMLAgg, XMLElement and XMLForest. But there are more options. You can use the dbms_xmlgen package for this purpose. And via a colleague who attended an AMIS session by Matthieu de Graaf early this year, I became aware of a third option. The XMLType constructor is heavily overloaded in its first argument, and one of the many possibilities is a SYS_REFCURSOR variable. See the documentation here. This opens up new opportunities. And searching a little more, I also found a fourth way using stylesheets and XMLTransform. Below I will give an example of those four methods, for easy referencing.

First a simple example: selecting two columns from a single table. Using the XML publishing functions:

rwijk@ORA11GR1> declare
2 l_xml_as_clob clob;
3 begin
4 select xmlserialize
5 ( document xmlroot
6 ( xmlelement
7 ( "Departments"
8 , xmlagg
9 ( xmlelement
10 ( "Department"
11 , xmlforest
12 ( deptno as "Number"
13 , dname as "Name"
14 )
15 )
16 )
17 )
18 ) as clob indent
19 )
20 into l_xml_as_clob
21 from dept
22 where deptno in (10,20)
23 ;
24 dbms_output.put_line(l_xml_as_clob);
25 end;
26 /
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The second variant using dbms_xmlgen, works likes this:

rwijk@ORA11GR1> declare
2 l_xml xmltype;
3 l_context_handle dbms_xmlgen.ctxHandle;
4 begin
5 l_context_handle := dbms_xmlgen.newContext
6 ( 'select d.deptno "Number"
7 , d.dname "Name"
8 from dept d
9 where d.deptno in (10,20)'
10 );
11 dbms_xmlgen.setRowSetTag(l_context_handle, 'Departments');
12 dbms_xmlgen.setRowTag(l_context_handle, 'Department');
13 l_xml := xmltype(dbms_xmlgen.getXML(l_context_handle));
14 dbms_xmlgen.closeContext(l_context_handle);
15 dbms_output.put_line(l_xml.getClobVal());
16 end;
17 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>


PL/SQL procedure successfully completed.


Note that the default ROWSET and ROW tagnames are replaced using the setRowTag and setRowSetTag procedures, by the more meaningful names Departments and Department.

The third variant using the XMLType constructor that accepts a ref cursor:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select deptno "Number"
8 , dname "Name"
9 from dept
10 where deptno in (10,20)
11 ;
12 l_xml := xmltype(l_rc);
13 close l_rc;
14 dbms_output.put_line(l_xml.getClobVal());
15 end;
16 /
<?xml version="1.0"?>
<ROWSET>
<ROW>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</ROW>
<ROW>
<Number>20</Number>
<Name>RESEARCH</Name>
</ROW>
</ROWSET>


PL/SQL procedure successfully completed.


Using the XML publishing functions you are free to use any tagname you want, but here we get standard names without nice functions to change the tagnames. We can use some aliases to get more tagnames right, but the ROWSET and ROW ones stay. There seems to be nothing else left than to use good old - and rather ugly - replace functions to get the same tagnames as in the first and second example:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select deptno "Number"
8 , dname "Name"
9 from dept
10 where deptno in (10,20)
11 ;
12 l_xml := xmltype(l_rc);
13 close l_rc;
14 dbms_output.put_line
15 ( replace
16 ( replace
17 ( replace
18 ( replace
19 ( l_xml.getClobVal()
20 , '<ROWSET>'
21 , '<Departments>'
22 )
23 , '</ROWSET>'
24 , '</Departments>'
25 )
26 , '<ROW>'
27 , '<Department>'
28 )
29 , '</ROW>'
30 , '</Department>'
31 )
32 );
33 end;
34 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The fourth option is to use XMLTransform and a stylesheet, like this:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 select xmlroot
6 ( xmltransform
7 ( xmltype
8 ( cursor
9 ( select d.deptno
10 , d.dname
11 from dept d
12 where d.deptno in (10,20)
13 )
14 )
15 , xmltype.createxml
16 ( '<?xml version="1.0"?>
17 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
18 <xsl:template match="/">
19 <Departments>
20 <xsl:for-each select="/ROWSET/ROW">
21 <Department>
22 <Number><xsl:value-of select="DEPTNO"/></Number>
23 <Name><xsl:value-of select="DNAME"/></Name>
24 </Department>
25 </xsl:for-each>
26 </Departments>
27 </xsl:template>
28 </xsl:stylesheet>'
29 )
30 )
31 )
32 into l_xml
33 from dual
34 ;
35 dbms_output.put_line(l_xml.getClobVal());
36 end;
37 /
<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
</Department>
</Departments>

PL/SQL procedure successfully completed.


So far, it's quite easy, because the query was easy. Now let's try to add some extras: a list of employees with their name and salary for each department. First with the XML publishing functions:

rwijk@ORA11GR1> declare
2 l_xml_as_clob clob;
3 begin
4 select xmlserialize
5 ( document xmlroot
6 ( xmlelement
7 ( "Departments"
8 , xmlagg
9 ( xmlelement
10 ( "Department"
11 , xmlforest
12 ( d.deptno as "Number"
13 , d.dname as "Name"
14 )
15 , xmlelement
16 ( "Employees"
17 , xmlagg
18 ( xmlelement
19 ( "Employee"
20 , xmlforest
21 ( e.ename as "Name"
22 , e.sal as "Salary"
23 )
24 )
25 )
26 )
27 )
28 )
29 )
30 ) as clob indent
31 )
32 into l_xml_as_clob
33 from dept d
34 , emp e
35 where d.deptno = e.deptno
36 and d.deptno in (10,20)
37 group by d.deptno
38 , d.dname
39 ;
40 dbms_output.put_line(l_xml_as_clob);
41 end;
42 /
<?xml version="1.0" encoding="WINDOWS-1252"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
</Employees>
</Department>
</Departments>


PL/SQL procedure successfully completed.


As you can see, the select list becomes increasingly complex. But once you manage to pull off the simple variant, you will likely succeed in more complex expressions as well.

Using dbms_xmlgen, it may not be immediately obvious how to handle such a master-detail type of datastructure in a single query. But my first try using cursor expressions was successful:

rwijk@ORA11GR1> declare
2 l_xml xmltype;
3 l_context_handle dbms_xmlgen.ctxHandle;
4 begin
5 l_context_handle := dbms_xmlgen.newContext
6 ( 'select d.deptno "Number"
7 , d.dname "Name"
8 , cursor
9 ( select e.ename "Name"
10 , e.sal "Salary"
11 from emp e
12 where e.deptno = d.deptno
13 ) "Employees"
14 from dept d
15 where d.deptno in (10,20)'
16 );
17 dbms_xmlgen.setRowSetTag(l_context_handle, 'Departments');
18 dbms_xmlgen.setRowTag(l_context_handle, 'Department');
19 l_xml := xmltype(dbms_xmlgen.getXML(l_context_handle));
20 dbms_xmlgen.closeContext(l_context_handle);
21 dbms_output.put_line
22 ( replace
23 ( replace
24 ( l_xml.getClobVal()
25 , '<Employees_ROW>'
26 , '<Employee>'
27 )
28 , '</Employees_ROW>'
29 , '</Employee>'
30 )
31 );
32 end;
33 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The only tag that needs to be adjusted with replace functions, is the "Employees_ROW" tag. By the way, be sure to give the cursor expression an alias, or you'll end up with horrible tagnames!

For the XMLType constructor using a sys_refcursor, it works almost the same:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select d.deptno "Number"
8 , d.dname "Name"
9 , cursor
10 ( select e.ename "Name"
11 , e.sal "Salary"
12 from emp e
13 where e.deptno = d.deptno
14 ) "Employees"
15 from dept d
16 where d.deptno in (10,20)
17 ;
18 l_xml := xmltype(l_rc);
19 close l_rc;
20 dbms_output.put_line
21 ( replace
22 ( replace
23 ( replace
24 ( replace
25 ( replace
26 ( replace
27 ( l_xml.getClobVal()
28 , '<Employees_ROW>'
29 , '<Employee>'
30 )
31 , '</Employees_ROW>'
32 , '</Employee>'
33 )
34 , '<ROWSET>'
35 , '<Departments>'
36 )
37 , '</ROWSET>'
38 , '</Departments>'
39 )
40 , '<ROW>'
41 , '<Department>'
42 )
43 , '</ROW>'
44 , '</Department>'
45 )
46 );
47 end;
48 /
<?xml version="1.0"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


PL/SQL procedure successfully completed.


The query and especially the select list, is much cleaner than the one using the XML publishing functions, but the replace functions are still very clumsy.

I cannot get the last alternative using XMLTransform and stylesheets to work. In SQL it doesn't give an error message:

rwijk@ORA11GR1> select xmlroot
2 ( xmltransform
3 ( xmltype
4 ( cursor
5 ( select deptno
6 , dname
7 , cursor
8 ( select ename
9 , sal
10 from emp
11 where emp.deptno = dept.deptno
12 ) emps
13 from dept
14 where deptno in (10,20)
15 )
16 )
17 , xmltype.createxml
18 ( '<?xml version="1.0"?>
19 <xsl:stylesheet version="1.0"
20 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
21 <xsl:template match="/">
22 <Departments>
23 <xsl:for-each select="/ROWSET/ROW">
24 <Department>
25 <Number><xsl:value-of select="DEPTNO"/></Number>
26 <Name><xsl:value-of select="DNAME"/></Name>
27 <Employees>
28 <xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
29 <Employee>
30 <Name><xsl:value-of select="ENAME"/></Name>
31 <Salary><xsl:value-of select="SAL"/></Salary>
32 </Employee>
33 </xsl:for-each>
34 </Employees>
35 </Department>
36 </xsl:for-each>
37 </Departments>
38 </xsl:template>
39 </xsl:stylesheet>'
40 )
41 )
42 ) xml
43 from dual
44 /

XML
----------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


1 row selected.


But if you have a closer look at the employees per department, then you'll see every employee now listed in both departments 10 and 20. It seems impossible to correlate correctly using this method. We have just asked the stylesheet to collect all employees and list them all for each department.

Another strange phenomenon: putting this exact SQL statement inside a PL/SQL block raises an error:

rwijk@ORA11GR1> declare
2 l_xml xmltype;
3 begin
4 select xmlroot
5 ( xmltransform
6 ( xmltype
7 ( cursor
8 ( select deptno
9 , dname
10 , cursor
11 ( select ename
12 , sal
13 from emp
14 where emp.deptno = dept.deptno
15 ) emps
16 from dept
17 where deptno in (10,20)
18 )
19 )
20 , xmltype.createxml
21 ( '<?xml version="1.0"?>
22 <xsl:stylesheet version="1.0"
23 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
24 <xsl:template match="/">
25 <Departments>
26 <xsl:for-each select="/ROWSET/ROW">
27 <Department>
28 <Number><xsl:value-of select="DEPTNO"/></Number>
29 <Name><xsl:value-of select="DNAME"/></Name>
30 <Employees>
31 <xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
32 <Employee>
33 <Name><xsl:value-of select="ENAME"/></Name>
34 <Salary><xsl:value-of select="SAL"/></Salary>
35 </Employee>
36 </xsl:for-each>
37 </Employees>
38 </Department>
39 </xsl:for-each>
40 </Departments>
41 </xsl:template>
42 </xsl:stylesheet>'
43 )
44 )
45 )
46 into l_xml
47 from dual
48 ;
49 dbms_output.put_line(l_xml.getClobVal());
50 end;
51 /
declare
*
ERROR at line 1:
ORA-22916: cannot do an exact FETCH on a query with Nested cursors
ORA-06512: at line 4


First extracting the XML including the nested cursors, and then applying the stylesheet, leads to another problem:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select d.deptno
8 , d.dname
9 , cursor
10 ( select e.ename
11 , e.sal
12 from emp e
13 where e.deptno = d.deptno
14 ) emps
15 from dept d
16 where d.deptno in (10,20)
17 ;
18 l_xml := xmltype(l_rc);
19 close l_rc;
20 open l_rc
21 for
22 select xmltransform
23 ( l_xml
24 , xmltype.createxml
25 ( '<:?xml version="1.0"?>
26 <:xsl:stylesheet version="1.0"
27 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
28 <:xsl:template match="/">
29 <:Departments>
30 <:xsl:for-each select="/ROWSET/ROW">
31 <:Department>
32 <:Number><:xsl:value-of select="DEPTNO"/><:/Number>
33 <:Name><:xsl:value-of select="DNAME"/><:/Name>
34 <:Employees>
35 <:xsl:for-each select="/ROWSET/ROW/EMPS/EMPS_ROW">
36 <:Employee>
37 <:Name><:xsl:value-of select="ENAME"/><:/Name>
38 <:Salary><:xsl:value-of select="SAL"/><:/Salary>
39 <:/Employee>
40 <:/xsl:for-each>
41 <:/Employees>
42 <:/Department>
43 <:/xsl:for-each>
44 <:/Departments>
45 <:/xsl:template>
46 <:/xsl:stylesheet>'
47 )
48 )
49 from dual
50 ;
51 l_xml := xmltype(l_rc);
52 close l_rc;
53 dbms_output.put_line(l_xml.getClobVal());
54 end;
55 /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00110: Warning: invalid QName "XMLTRANSFORM_x0028_:B1_x002C_XMLTYPE.CREATEXML_x0
028__x0027__x003C__x003F_XMLVERSION_x003D__x0022_1.0_x0022__x003F__x003E__x003C_XSL:
STYLESHEETVERSION_x003D__x0022_1.0_x0022_XMLNS:XSL_x003D__x0022_HTTP:_x002F__x002F_.
..
Error at line 4
ORA-06512: at "SYS.XMLTYPE", line 343
ORA-06512: at line 51


This path is getting worse every step. The stylesheets have been an interesting journey, but there are at least three far less cumbersome alternatives to choose from.



UPDATE


With the help of Jason (see comments section below), I've got the fourth variant using stylesheet transformation working for both SQL and PL/SQL. In SQL the results become correlated again by specifying a path in the inner "for-each select" within the path of the outer "for-each select":

rwijk@ORA11GR1> select xmlroot
2 ( xmltransform
3 ( xmltype
4 ( cursor
5 ( select deptno
6 , dname
7 , cursor
8 ( select ename
9 , sal
10 from emp
11 where emp.deptno = dept.deptno
12 ) emps
13 from dept
14 where deptno in (10,20)
15 )
16 )
17 , xmltype.createxml
18 ( '<?xml version="1.0"?>
19 <xsl:stylesheet version="1.0"
20 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
21 <xsl:template match="/">
22 <Departments>
23 <xsl:for-each select="/ROWSET/ROW">
24 <Department>
25 <Number><xsl:value-of select="DEPTNO"/></Number>
26 <Name><xsl:value-of select="DNAME"/></Name>
27 <Employees>
28 <xsl:for-each select="EMPS/EMPS_ROW">
29 <Employee>
30 <Name><xsl:value-of select="ENAME"/></Name>
31 <Salary><xsl:value-of select="SAL"/></Salary>
32 </Employee>
33 </xsl:for-each>
34 </Employees>
35 </Department>
36 </xsl:for-each>
37 </Departments>
38 </xsl:template>
39 </xsl:stylesheet>'
40 )
41 )
42 ) xml
43 from dual
44 /

XML
-------------------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>


1 rij is geselecteerd.


In PL/SQL you have to separate the stylesheet transformation from the XML-izing of the data and use XMLType() instead of the createxml-method:

rwijk@ORA11GR1> declare
2 l_rc sys_refcursor;
3 l_xml xmltype;
4 begin
5 open l_rc
6 for
7 select d.deptno
8 , d.dname
9 , cursor
10 ( select e.ename
11 , e.sal
12 from emp e
13 where e.deptno = d.deptno
14 ) emps
15 from dept d
16 where d.deptno in (10,20)
17 ;
18 l_xml := xmltype(l_rc);
19 close l_rc
20 ;
21 select xmlroot
22 ( xmltransform
23 ( l_xml
24 , xmltype
25 ( '<?xml version="1.0"?>
26 <xsl:stylesheet version="1.0"
27 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
28 <xsl:template match="/">
29 <Departments>
30 <xsl:for-each select="/ROWSET/ROW">
31 <Department>
32 <Number><xsl:value-of select="DEPTNO"/></Number>
33 <Name><xsl:value-of select="DNAME"/></Name>
34 <Employees>
35 <xsl:for-each select="EMPS/EMPS_ROW">
36 <Employee>
37 <Name><xsl:value-of select="ENAME"/></Name>
38 <Salary><xsl:value-of select="SAL"/></Salary>
39 </Employee>
40 </xsl:for-each>
41 </Employees>
42 </Department>
43 </xsl:for-each>
44 </Departments>
45 </xsl:template>
46 </xsl:stylesheet>'
47 )
48 )
49 )
50 into l_xml
51 from dual
52 ;
53 dbms_output.put_line(l_xml.getClobVal());
54 end;
55 /
<?xml version="1.0" encoding="UTF-8"?>
<Departments>
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Employees>
<Employee>
<Name>CLARK</Name>
<Salary>2450</Salary>
</Employee>
<Employee>
<Name>KING</Name>
<Salary>5000</Salary>
</Employee>
<Employee>
<Name>MILLER</Name>
<Salary>1300</Salary>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Employees>
<Employee>
<Name>SMITH</Name>
<Salary>800</Salary>
</Employee>
<Employee>
<Name>JONES</Name>
<Salary>2975</Salary>
</Employee>
<Employee>
<Name>SCOTT</Name>
<Salary>3000</Salary>
</Employee>
<Employee>
<Name>ADAMS</Name>
<Salary>1100</Salary>
</Employee>
<Employee>
<Name>FORD</Name>
<Salary>3000</Salary>
</Employee>
</Employees>
</Department>
</Departments>

PL/SQL-procedure is geslaagd.

Tuesday, February 10, 2009

What's in my JMS queue?

If you have setup a JMS queue with Oracle's Advanced Queuing, a question that pops up regularly is: "What's currently in the queue?". Now that can't be hard - and it isn't - yet I spent a silly amount of time getting it done. Hopefully this blog post will prevent others spending too much time.

To setup the JMS queue, create a queue table with payload type sys.aq$_jms_text_message, and a queue in that queue table:

rwijk@ORA11GR1> begin
2 dbms_aqadm.create_queue_table
3 ( queue_table => 'my_queue_table'
4 , queue_payload_type => 'sys.aq$_jms_text_message'
5 );
6 dbms_aqadm.create_queue
7 ( queue_name => 'my_queue'
8 , queue_table => 'my_queue_table'
9 );
10 dbms_aqadm.start_queue
11 ( queue_name => 'my_queue'
12 );
13 end;
14 /

PL/SQL procedure successfully completed.


Next, put a message on the queue:

rwijk@ORA11GR1> declare
2 l_enqueue_options dbms_aq.enqueue_options_t;
3 l_message_properties dbms_aq.message_properties_t;
4 l_message sys.aq$_jms_text_message;
5 l_msgid raw(16);
6 begin
7 l_message := sys.aq$_jms_text_message.construct;
8 l_message.set_text(xmltype('<emp><ename>ROB</ename></emp>').getClobVal());
9 dbms_aq.enqueue
10 ( queue_name => 'my_queue'
11 , enqueue_options => l_enqueue_options
12 , message_properties => l_message_properties
13 , payload => l_message
14 , msgid => l_msgid
15 );
16 commit;
17 end;
18 /

PL/SQL procedure successfully completed.


You can query a queue by querying the queue table and filtering on the q_name column, like this:

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , user_data
5 from my_queue_table
6 where q_name = 'MY_QUEUE'
7 /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA(HEADER(REPLYTO(NAME, ADDRESS, PROTOCOL), TYPE, USERID, APPID, GROUPID,
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
AQ$_JMS_TEXT_MESSAGE(AQ$_JMS_HEADER(NULL, NULL, NULL, NULL, NULL, NULL, NULL), 2
9, '<emp><ename>ROB</ename></emp>', NULL)


1 row selected.


At work I use an older SQL*Plus version, that is not able to show the contents of the user_data column, because of some LOB content. No problem, as I am really only interested in the text_vc field of the sys.aq$_jms_text_message object. So just query that field:

rwijk@ORA11GR1> desc my_queue_table
Name Null? Type
----------------------------------------- -------- ------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
CORRID VARCHAR2(128)
PRIORITY NUMBER
STATE NUMBER
DELAY TIMESTAMP(6)
EXPIRATION NUMBER
TIME_MANAGER_INFO TIMESTAMP(6)
LOCAL_ORDER_NO NUMBER
CHAIN_NO NUMBER
CSCN NUMBER
DSCN NUMBER
ENQ_TIME TIMESTAMP(6)
ENQ_UID VARCHAR2(30)
ENQ_TID VARCHAR2(30)
DEQ_TIME TIMESTAMP(6)
DEQ_UID VARCHAR2(30)
DEQ_TID VARCHAR2(30)
RETRY_COUNT NUMBER
EXCEPTION_QSCHEMA VARCHAR2(30)
EXCEPTION_QUEUE VARCHAR2(30)
STEP_NO NUMBER
RECIPIENT_KEY NUMBER
DEQUEUE_MSGID RAW(16)
SENDER_NAME VARCHAR2(30)
SENDER_ADDRESS VARCHAR2(1024)
SENDER_PROTOCOL NUMBER
USER_DATA SYS.AQ$_JMS_TEXT_MESSAGE
USER_PROP SYS.ANYDATA

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , user_data.text_vc
5 from my_queue_table
6 where q_name = 'MY_QUEUE'
7 /
, user_data.text_vc
*
ERROR at line 4:
ORA-00904: "USER_DATA"."TEXT_VC": invalid identifier


And this message puzzled me for a quite a while. After an extensive search, an AskTom thread provided the answer. A very simple answer: you need to prefix the expression with a table alias:

rwijk@ORA11GR1> select msgid
2 , enq_time
3 , enq_uid
4 , qt.user_data.text_vc
5 from my_queue_table qt
6 where q_name = 'MY_QUEUE'
7 /

MSGID
--------------------------------
ENQ_TIME
---------------------------------------------------------------------------
ENQ_UID
------------------------------
USER_DATA.TEXT_VC
--------------------------------------------------------------------------------
40279F37C785499CB386CC47ABCAAB1C
10-FEB-09 11.59.16.312000 PM
RWIJK
<emp><ename>ROB</ename></emp>


1 row selected.

Saturday, February 7, 2009

FOR UPDATE SKIP LOCKED

The behaviour of the "for update skip locked" clause is not what I expected it to be. I expected it to try to lock all rows when opening a cursor, just like a regular "for update", and then just skipping the locked rows. Below is a test to show how it works in reality. The setup:

A table with three rows:

rwijk@ORA11GR1> create table t (id)
2 as
3 select level
4 from dual
5 connect by level <= 3
6 /

Tabel is aangemaakt.


A procedure to check if a record of this table t is locked:

rwijk@ORA11GR1> create procedure check_record_locked
2 ( p_id in t.id%type
3 )
4 is
5 cursor c
6 is
7 select 'dummy'
8 from t
9 where id = p_id
10 for update nowait
11 ;
12 e_resource_busy exception;
13 pragma exception_init(e_resource_busy,-54);
14 begin
15 open c;
16 close c;
17 dbms_output.put_line('Record ' || to_char(p_id) || ' is not locked.');
18 rollback;
19 exception
20 when e_resource_busy then
21 dbms_output.put_line('Record ' || to_char(p_id) || ' is locked.');
22 end check_record_locked;
23 /

Procedure is aangemaakt.


First, let's examine the default behaviour of the regular "for update" clause. During the 10 seconds sleep, I copy, paste and execute the code in the comments in another SQL*Plus session.

rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update;
3 l_id t.id%type;
4 begin
5 open c;
6 fetch c into l_id;
7 dbms_lock.sleep(10);
8 --
9 -- In another session run:
10 --
11 -- begin
12 -- check_record_locked(1);
13 -- check_record_locked(2);
14 -- check_record_locked(3);
15 -- end;
16 -- /
17 --
18 close c;
19 end;
20 /

PL/SQL-procedure is geslaagd.


The output from the other session:

rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 end;
6 /
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.


As expected, the opening of the cursor locks rows 1 and 2. Whether the record is fetched or not, has no influence.

Now a similar test with the "for update skip locked" clause:

rwijk@ORA11GR1> rollback
2 /

Rollback is voltooid.

rwijk@ORA11GR1> declare
2 cursor c is select id from t where id <= 2 for update skip locked;
3 l_id t.id%type;
4 begin
5 open c;
6 dbms_lock.sleep(10);
7 --
8 -- In another session run:
9 --
10 -- begin
11 -- check_record_locked(1);
12 -- check_record_locked(2);
13 -- check_record_locked(3);
14 -- dbms_lock.sleep(10);
15 -- check_record_locked(1);
16 -- check_record_locked(2);
17 -- check_record_locked(3);
18 -- dbms_lock.sleep(10);
19 -- check_record_locked(1);
20 -- check_record_locked(2);
21 -- check_record_locked(3);
22 -- end;
23 -- /
24 --
25 fetch c into l_id;
26 dbms_lock.sleep(10);
27 fetch c into l_id;
28 dbms_lock.sleep(10);
29 close c;
30 end;
31 /

PL/SQL-procedure is geslaagd.


During the first sleep of 10 seconds, the code in the comments is again copied, pasted and executed in a second SQL*Plus session. Note that it prints the situation after just having opened the cursor, after the first fetch and after the second fetch. This is the output:

rwijk@ORA11GR1> begin
2 check_record_locked(1);
3 check_record_locked(2);
4 check_record_locked(3);
5 dbms_lock.sleep(10);
6 check_record_locked(1);
7 check_record_locked(2);
8 check_record_locked(3);
9 dbms_lock.sleep(10);
10 check_record_locked(1);
11 check_record_locked(2);
12 check_record_locked(3);
13 end;
14 /
Record 1 is not locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is not locked.
Record 3 is not locked.
Record 1 is locked.
Record 2 is locked.
Record 3 is not locked.

PL/SQL-procedure is geslaagd.


So opening a cursor with the "for update skip locked" clause doesn't lock a single row. Fetching a row does lock the row, as can be seen in the output. This was the first surprise: I couldn't find text in the documentation telling about this behaviour.

But won't this behaviour lead to a giant "lost update" problem? When you change a record in a second session between the open and the fetch of the cursor - which is allowed because the record is not locked - the cursor could overwrite this change because it has retrieved the value from before the update. Let's check how this works. The setup is a table with bank accounts containing 5 records with an identifying number, an amount and an indicator telling whether interest has been calculated already:

rwijk@ORA11GR1> create table bankaccounts(nr,amount,interest_calculated_indicator)
2 as
3 select level
4 , 100
5 , 'N'
6 from dual
7 connect by level <= 5
8 /

Tabel is aangemaakt.


First a test using the "for update" clause. A piece of code that calculates interest by multiplying the amount of all bankaccounts with the indicator set to 'N' by 5%. During the sleep of 10 seconds, the commented code is copied, pasted and executed in a second SQL*Plus session:

rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /

PL/SQL-procedure is geslaagd.


The second session has to wait with the update. So all amounts are set to 105.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 105 Y
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.


After this commit, the second session continues and produces this output:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 205 Y
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.


Everything as expected. But now the same code using a "for update skip locked". First restore the situation to its initial state:

rwijk@ORA11GR1> remark  Restore situation
rwijk@ORA11GR1> update bankaccounts
2 set amount = 100
3 , interest_calculated_indicator = 'N'
4 /

5 rijen zijn bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.


And execute the same code with a "for update skip locked":

rwijk@ORA11GR1> declare
2 cursor c
3 is
4 select nr
5 , amount
6 from bankaccounts
7 where interest_calculated_indicator = 'N'
8 for update skip locked
9 ;
10 type ta_nr is table of bankaccounts.nr%type;
11 type ta_amount is table of bankaccounts.amount%type;
12 a_nr ta_nr;
13 a_amount ta_amount;
14 begin
15 open c;
16 dbms_lock.sleep(10);
17 --
18 -- In another session, try this:
19 --
20 -- update bankaccounts set amount = amount + 100 where nr = 2
21 -- /
22 -- commit
23 -- /
24 -- select * from bankaccounts
25 -- /
26 --
27 loop
28 fetch c bulk collect into a_nr,a_amount limit 2;
29 forall i in 1..a_nr.count
30 update bankaccounts
31 set amount = a_amount(i) * 1.05
32 , interest_calculated_indicator = 'Y'
33 where nr = a_nr(i)
34 ;
35 exit when a_nr.count <> 2;
36 end loop;
37 end;
38 /

PL/SQL-procedure is geslaagd.


The second session doesn't have to wait, and the immediate response in this second SQL*Plus session is:

rwijk@ORA11GR1> update bankaccounts set amount = amount + 100 where nr = 2
2 /

1 rij is bijgewerkt.

rwijk@ORA11GR1> commit
2 /

Commit is voltooid.

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 100 N
2 200 N
3 100 N
4 100 N
5 100 N

5 rijen zijn geselecteerd.


Now I thought the output in the first session would show a lost update: the amount in record 2 being 105. But it didn't:

rwijk@ORA11GR1> select * from bankaccounts
2 /

NR AMOUNT I
---------- ---------- -
1 105 Y
2 200 N
3 105 Y
4 105 Y
5 105 Y

5 rijen zijn geselecteerd.


It just didn't process the second row. So a "for update skip locked" doesn't only skip the locked rows, it also skips the rows that were modified between the opening and the fetching, thus avoiding the classic "lost update".

Thanks to James Padfield for hinting at this behaviour.

Friday, January 30, 2009

COUNT DISTINCT

This morning I saw an e-mail with the title "SQL puzzle?" in my inbox. That's a good title to make me read that one first. The poster was stunned by the results of the following two queries:

SQL> SELECT COUNT(DISTINCT bdl.nrl_id)
2 FROM a_table bdl
3 WHERE bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')
4 ;

COUNT(DISTINCTBDL.NRL_ID)
-------------------------
61111

1 rij is geselecteerd.

SQL> SELECT COUNT(*)
2 FROM (SELECT DISTINCT bdl.nrl_id
3 FROM a_table bdl
4 WHERE bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')
5 )
6 ;

COUNT(*)
----------
61112

1 rij is geselecteerd.


Of course it's possible that a record was inserted in the meantime, but that was not the case here.

The explanation was quite simple. See this example:

rwijk@ORA11GR1> select comm from emp
2 /

COMM
----------

300
500

1400




0





14 rijen zijn geselecteerd.

rwijk@ORA11GR1> select distinct comm from emp
2 /

COMM
----------

1400
500
300
0

5 rijen zijn geselecteerd.

rwijk@ORA11GR1> select count(*) from (select distinct comm from emp)
2 /

COUNT(*)
----------
5

1 rij is geselecteerd.

rwijk@ORA11GR1> select count(distinct comm) from emp
2 /

COUNT(DISTINCTCOMM)
-------------------
4

1 rij is geselecteerd.


Again those pesky null values. From the documentation:
"All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls."
A distinct doesn't ignore nulls, and the count(*) on top of it doesn't either.

At first glance, a "select count(distinct ...)" appears equal to a "select count(*) from (select distinct ...)", but as shown: it is not.



UPDATE


I forgot to mention that a "select count(comm) from (select distinct comm...)" [note: count(comm) instead of count(*)] is equal to a "select count(distinct comm) ...":

rwijk@ORA11GR1> select count(comm) from (select distinct comm from emp);

COUNT(COMM)
-----------
4

1 rij is geselecteerd.

Monday, January 19, 2009

PL/SQL session

Today I gave a session about PL/SQL to colleagues who mostly work with Tibco in our Enterprise Application Integration (EAI) group. They know how to program and they know SQL. The challenge was to cover most of the main features of PL/SQL in a workshop in only 8 hours. The session contained lots of little exercises. Topics were architecture, datatypes, main statements, exception handling, appearances of PL/SQL, SQL in PL/SQL, cursors, collections and recordtypes, bulk processing and dynamic SQL.

For those who are interested and who can read Dutch: you can download the document, presentation and exercises here:

PL/SQL-sessie

Bijbehorende powerpoint voor hoofdstuk 1

Alle scripts

Tuesday, January 6, 2009

SQL Model Clause tutorial, part three

SQL Model Clause Tutorial, part one
SQL Model Clause Tutorial, part two

If you prefer to read in Dutch, then you can read the original article here (page 30-34), and if you prefer to read in German, then you can find a translated version here.

Practicalities of the SQL Model Clause

If developing SQL queries is part of your job, then it is definitely worth knowing the SQL model clause. Once you’ve gotten over the initial fear of the new syntax and learned the basics, you can save yourself writing procedural code and creating auxiliary database objects even more often, by doing more in SQL itself. You may end up with shorter, more readable and sometimes faster code. This article will show a couple of uses of the model clause for fairly common type of queries and will discuss in each case whether or not the model clause solution is suitable compared to alternative queries - if any - in terms of readability and performance. By the end you will more easily recognize situations in your daily work where you can apply the model clause.

Background / Overview

The SQL model clause was introduced with the release of Oracle10g Release 1, back in 2003. According to the documentation, the model clause can replace PC-based spreadsheets by creating a multidimensional array from query results and then apply formulas (called rules) to this array to calculate new values. However, judging from the various Oracle forums, it is not used very often yet. And when it is used, people are not doing spreadsheet like calculations with it. The types of queries the model clause does get used, are discussed in this article. These types of queries are:

  • Forecasting queries
  • Row generation
  • Variable number of calculations based on calculated values
  • Complex algorithms
  • String aggregation
Of course, the possibilities of the model clause are certainly not limited to this list.

If you are not familiar with the SQL model clause yet, then you can catch up by reading Chapter 22 of the Data Warehousing Guide. You can also read the step-by-step tutorials on my weblog here and here.

Forecasting queries

This type of query is used throughout the aforementioned chapter 22 of the Oracle Database Data Warehousing Guide. The basis for this type of query is typically a sales table or view and the question to be answered is a forecast of how future sales will look like. For example, let’s have a look at the SALES table in figure 1.

PRODUCT       YEAR     AMOUNT
------- ---------- ----------
paper         2006          2
pen           2006         80
staples       2006         18
paper         2007          4
pen           2007        100
staples       2007         30

And suppose you want to calculate the 2008 sales to be the 2007 sales multiplied by the same growth factor as from 2006 to 2007. In a formula:

sales[2008] = sales[2007] * ( sales[2007] / sales[2006] )

Using the model clause, such a question can be answered like this:
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

Using the model clause for such a query is not mandatory; you can achieve the same result by doing a UNION ALL and calculate next year’s sales in a separate query with its own extra table access. Or you can use grouping sets. But if you write these statements, you'll see they become messy and complicated quite fast. And what if you want the sales forecast for 2009 as well, for example by multiplying the sales of 2008 by 2? With the model clause, you just add an extra rule:

amount[2009] = amount[2008] * 2

So forecasting queries are the example in chapter 22 of the Data Warehousing Guide for a reason, as the SQL model clause is able to calculate them without much ado.

Row generation

For many years, you generated rows by selecting from a table or view for which you knew it contained at least the amount of rows you wanted to generate. Usually the all_objects view was used for this, like this:
select rownum
  from all_objects
 where rownum <= 100000

Then Mikito Harakiri came up with this simple row generating query against dual:
 select level
   from dual
connect by level <= 100000

And with the model clause there is another alternative:
select i
  from dual
 model
       dimension by (1 i)
       measures (0 x)
       (x[for i from 2 to 100000 increment 1] = 0)

And this is a tkprof snippet of a 10046 level 8 trace of the three variants generating 100,000 rows:
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



********************************************************************************

The model clause query for generating rows is faster than the good old query against all_objects, but much slower than the the "connect by level" query. It is therefore not worthwhile to use the SQL model clause for pure row generating. However, for inferred problems of row generation, the model clause can certainly be an option. An example of such an inferred problems is splitting periods into individual days in a calendar, for example when 1 row containing a startdate January 1st and enddate January 5th, have to be split into 5 individual rows. Another example is splitting strings into individual words:

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  /

Table created.

To split the individual words from these strings you can use one of the next two queries:

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 rows selected.

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 rows selected.

You can see how they are based on the pure row generating queries described earlier. In these solutions "regexp_count(str,' ')+1" calculates the number of words, by calculating the number of spaces and adding one. "regexp_substr(str,'[^ ]+',1,n)" gets the n-th word out of the string.

Especially for the "connect by level" query, more variants are possible. All of them look pretty cryptic, making them hard to maintain. The SQL model clause query maintains its readability by using the partition clause.

With a 10,000 times enlarged table t, the tkprof file looks like this:

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)

********************************************************************************

Again, the "connect by level" trick wins, but this time it's much closer. So you may let readability prevail.

Variable number of calculations based on calculated values

There are queries out there that just seem impossible to do with just SQL, where a model clause can provide a solution elegantly. This is a class of problems where calculations have to be done on calculated values repeatedly. An example says more than a thousand words, so here is one:
SQL> select * from deposits
  2  /

  CUSTOMER     AMOUNT DEPOSIT_DATE
---------- ---------- -------------------
         1       1000 01-01-2003 00:00:00
         1        200 01-01-2004 00:00:00
         1        500 01-01-2005 00:00:00
         1        100 01-01-2006 00:00:00
         1        800 01-01-2007 00:00:00
         2         20 01-01-2003 00:00:00
         2        150 01-01-2004 00:00:00
         2         60 01-01-2005 00:00:00
         2        100 01-01-2006 00:00:00
         2        100 01-01-2007 00:00:00

10 rows selected.

SQL> select * from interest_rates
  2  /

STARTDATE           PERCENTAGE
------------------- ----------
01-01-2003 00:00:00          5
01-01-2004 00:00:00        3.2
01-01-2005 00:00:00        4.1
01-01-2006 00:00:00        5.8
01-01-2007 00:00:00        4.9

5 rows selected.

The question is: "What's the balance at the end of each year?". For customer 1 at the end of 2003 it is: 1000 * 1,05 = 1050. At the end of 2004 this is (1050 + 200) * 1,032 = 1290, et cetera. This is what I mean by calculations based on calculated values, that you have to do a variable number of times. Using the model clause, this query becomes relatively easy, like this:

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 01-01-2003 00:00:00          5                   1050
         1        200 01-01-2004 00:00:00        3.2                   1290
         1        500 01-01-2005 00:00:00        4.1                1863.39
         1        100 01-01-2006 00:00:00        5.8                2077.27
         1        800 01-01-2007 00:00:00        4.9                3018.26
         2         20 01-01-2003 00:00:00          5                     21
         2        150 01-01-2004 00:00:00        3.2                 176.47
         2         60 01-01-2005 00:00:00        4.1                 246.17
         2        100 01-01-2006 00:00:00        5.8                 366.25
         2        100 01-01-2007 00:00:00        4.9                  489.1

10 rows selected.

For doing this type of calculations in SQL, there is hardly an alternative, except a very slow one using advanced XML functions. Before Oracle10 you'd probably resort to a procedural language such as PL/SQL. But nowadays you don't need to anymore.

Complex algorithms

With the SQL model clause, it has become possible to solve algorithms, that previously could not be done in SQL. Even some of the most complex ones. An example is a query to distribute tablespaces evenly among data files of a database, in such a way that the free space in each data file remains as equal as possible. Or even a single query to solve each sudoku puzzle when supplied with a string of 81 digits. Although it can be great fun to write, you'll have to admit that these will be a nightmare to maintain if you are not the original author. Regardless of reduced lines of code or small performance gains, a (pipelined) PL/SQL function seems much more appropriate here.

String aggregation

String aggregation is really just a special case of the category "variable number of calculations based on calculations". But it is such a frequently asked question on Oracle forums, that I will treat it separately.

String aggregation is the act of aggregating several rows into one row and "stringing up" all column values, possibly separated by a special character like a comma. An example with the well known EMP table is to produce the following output:

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

There are lots of ways to do string aggregation:
• using a user defined aggregate function, for example Tom Kyte’s stragg function,
• a hierarchical query with sys_connect_by_path
• with XML-functions
• using the COLLECT function, a SQL type and a user defined function
• using the undocumented (and therefore not recommended) wmsys.wm_concat

Using the model clause it is done like this:

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,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Just one query, no auxiliary objects and faster than all other previously mentioned alternatives. Nothing comes for free: the price to pay is a higher PGA memory footprint. Usually this is not a problem, if sufficient memory is available of course. If this is not the case, you'll notice the wait events 'direct path read temp' and 'direct path write temp' and the performance gain quickly turns around and becomes a performance loss.

Conclusion

For a number of problems, the SQL model clause is a very useful extension of the SQL language. This is especially true for forecasting queries and queries with a variable number of calculations based on calculated values, including string aggregation. The model clause can also be used for solving complex algorithms in SQL and for row generation. But this compromises maintainability in the first case and performance in the second case. Nevertheless, if you want to leverage SQL's capabilities, the model clause is absolutely worth studying and using.