Fast refreshable materialized view errors, part three: aggregate MV's

Previous post about join MV's

In the third part I'm going to examine all restrictions for aggregate materialized views, as described in the documentation.

For convenience of this post, I have numbered and slightly rearranged them to contain one restriction per number:

1) All restrictions from "General Restrictions on Fast Refresh".
2) All tables in the materialized view must have materialized view logs
3) The materialized view logs must contain all columns from the table referenced in the materialized view.
4) None of the columns in the base table, referred to in the materialized view log, can be encrypted.
5) All tables in the materialized view must have materialized view logs specified with ROWID
6) All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.
7) All tables in the materialized view must have materialized view logs, specified with the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
8) Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.
9) COUNT(*) must be specified.
10) Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
11) For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified.
12) If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.
13) The SELECT list must contain all GROUP BY columns.
14) If the materialized view has MIN or MAX aggregates, then fast refresh is supported only on conventional DML inserts and direct loads.
15) If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads.
16) If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads.
17) A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.
18) Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.
19) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.
20) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".
21) If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.
22) The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.
23) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified.
24) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.
25) If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

As mentioned in the first blogpost in this series, there is an extra restriction regarding aggregate MV's:

26) It cannot contain a HAVING clause with a subquery.

So this will be quite a lengthy and even tedious post, as you can imagine by the list above ... but for a good cause :-)

First, let's start with a base situation containing an aggregate MV:

rwijk@ORA11GR1> create table myemp as select * from emp
  2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO        CNT
---------- ----------
        30          6
        20          5
        10          3

3 rows selected.

And to prove that the MV is fast refreshed on commit:

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
  2    values (7777,'VAN WIJK','JANITOR',500,10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO        CNT
---------- ----------
        30          6
        20          5
        10          4

3 rows selected.

1) All restrictions from "General Restrictions on Fast Refresh".

These have been examined in part one.


2) All tables in the materialized view must have materialized view logs

This one is quite obvious, but I'm still executing it anyway to collect the error message.

rwijk@ORA11GR1> delete myemp where empno = 7777
  2  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  /
  from myemp
       *
ERROR at line 6:
ORA-23413: table "RWIJK"."MYEMP" does not have a materialized view log

The error message is clear as to what you should be doing to resolve this situation.


3) The materialized view logs must contain all columns from the table referenced in the materialized view.

To test this one, I create the materialized view log without the deptno column:

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  /
  from myemp
       *
ERROR at line 6:
ORA-12033: cannot use filter columns from materialized view log on
"RWIJK"."MYEMP"

And the materialized view does not get created, and with quite a clear error message, although mentioning a missing column would be convenient.


4) None of the columns in the base table, referred to in the materialized view log, can be encrypted.

I never did encryption of columns before, but it's actually quite easy to setup. First I add this line to my sqlnet.ora to specify the location of my wallet file:

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\product\11.1.0\mydb2\NETWORK\ADMIN\)))

Then I set the encryption key once. For subsequent runs it is not needed to set anymore, so I commented it out. The step creates a file called "ewallet.p12" in the location specified in the previous step.

rwijk@ORA11GR1> --alter system set encryption key identified by rwijk
rwijk@ORA11GR1> --/

Next action is to open the encryption wallet and encrypt the deptno column:

rwijk@ORA11GR1> alter system set encryption wallet open identified by rwijk
  2  /

System altered.

rwijk@ORA11GR1> alter table myemp modify (deptno encrypt)
  2  /

Table altered.

Now everything is ready to do the test:

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno) including new values
  3  /
create materialized view log on myemp
*
ERROR at line 1:
ORA-32412: encrypted column "DEPTNO" not allowed in the materialized view log


rwijk@ORA11GR1> alter system set encryption wallet close
  2  /

System altered.

The error message cannot be more clear.


5) All tables in the materialized view must have materialized view logs specified with ROWID.

rwijk@ORA11GR1> drop table myemp purge
  2  /

Table dropped.

rwijk@ORA11GR1> create table myemp as select * from emp
  2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with sequence (deptno) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  /
  from myemp
       *
ERROR at line 6:
ORA-12032: cannot use rowid column from materialized view log on
"RWIJK"."MYEMP"

Clear.


6) All tables in the materialized view must have materialized view logs specified with INCLUDING NEW VALUES.

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno)
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  /
  from myemp
       *
ERROR at line 6:
ORA-32401: materialized view log on "RWIJK"."MYEMP" does not have new values

Again clear.


7) All tables in the materialized view must have materialized view logs, specified with the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

I cannot reproduce this one, although I must admit that I did try, but not too hard. For a simple case it just works:

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO        CNT
---------- ----------
        30          6
        20          5
        10          3

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
  2    values (7777,'VAN WIJK','JANITOR',500,10)
  3  /

1 row created.

rwijk@ORA11GR1> delete myemp
  2   where empno = 7777
  3  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO        CNT
---------- ----------
        30          6
        20          5
        10          3

3 rows selected.

But you'd better specify the sequence clause in production code, after you've been warned by the documentation.


8) Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.

Let's try another aggregate function: the MAX...KEEP...FIRST/LAST one.

rwijk@ORA11GR1> drop table myemp purge
  2  /

Table dropped.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create table myemp as select * from emp
  2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno,sal,hiredate) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , max(sal) keep (dense_rank first order by hiredate) sal_first_hired_emp
  6       , count(*) cnt
  7    from myemp
  8   group by deptno
  9  /
  from myemp
       *
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

We've seen the ORA-12054 before...


9) COUNT(*) must be specified.

This one doesn't reproduce for simple examples:

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno,sal) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6    from myemp
  7   group by deptno
  8  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL
---------- ----------
        30       9400
        20      10875
        10       8750

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
  2    values (7777,'VAN WIJK','JANITOR',500,10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL
---------- ----------
        30       9400
        20      10875
        10       9250

3 rows selected.

It simply works, but again: I'd still specify count(*) in production code.


10) Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

rwijk@ORA11GR1> delete myemp where empno = 7777
  2  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , avg(sal) + avg(sal) twice_avg_sal
  6       , count(*) cnt
  7    from myemp
  8   group by deptno
  9  /
  from myemp
       *
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Again, an ORA-12054.


11) For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified.

For the below materialized view, COUNT(sal) must be present according to this restriction, but:

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6       , count(*) cnt
  7    from myemp
  8   group by deptno
  9  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL        CNT
---------- ---------- ----------
        30       9400          6
        20      10875          5
        10       8750          3

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
  2    values (7777,'VAN WIJK','JANITOR',500,10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL        CNT
---------- ---------- ----------
        30       9400          6
        20      10875          5
        10       9250          4

3 rows selected.

Again it just works and again I'd still add the COUNT(expr) anyway.


12) If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified.

An example where I don't specify the SUM(sal):

rwijk@ORA11GR1> delete myemp where empno = 7777
  2  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , variance(sal) varsal
  6       , count(sal) cntsal
  7       , count(*) cnt
  8    from myemp
  9   group by deptno
 10  /
  from myemp
       *
ERROR at line 8:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

And again the multifunctional ORA-12054.


13) The SELECT list must contain all GROUP BY columns.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select count(*) cnt
  5    from myemp
  6   group by deptno
  7  /
  from myemp
       *
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Again, ORA-12054.


14) If the materialized view has MIN or MAX aggregates, then fast refresh is supported only on conventional DML inserts and direct loads.

Let's try an update, as that should be impossible with aggregate MV's containing a MIN or MAX:

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , max(sal) maxsal
  6       , count(sal) cntsal
  7       , count(*) cnt
  8    from myemp
  9   group by deptno
 10  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     MAXSAL     CNTSAL        CNT
---------- ---------- ---------- ----------
        30       2850          6          6
        20       3000          5          5
        10       5000          3          3

3 rows selected.

rwijk@ORA11GR1> update myemp
  2     set sal = 0
  3   where ename = 'KING'
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     MAXSAL     CNTSAL        CNT
---------- ---------- ---------- ----------
        30       2850          6          6
        20       3000          5          5
        10       2450          3          3

3 rows selected.

And it works, but when you use it and it doesn't work, Oracle can say that you've been warned by the documentation :-)


15) If the materialized view has SUM(expr) but no COUNT(expr), then fast refresh is supported only on conventional DML inserts and direct loads.

This one contains SUM(sal), but no COUNT(sal):

rwijk@ORA11GR1> update myemp
  2     set sal = 5000
  3   where ename = 'KING'
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6       , count(*) cnt
  7    from myemp
  8   group by deptno
  9  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL        CNT
---------- ---------- ----------
        30       9400          6
        20      10875          5
        10       8750          3

3 rows selected.

rwijk@ORA11GR1> update myemp
  2     set sal = 0
  3   where ename = 'KING'
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL        CNT
---------- ---------- ----------
        30       9400          6
        20      10875          5
        10       8750          3

3 rows selected.

A nice example of a phenomenon I've experience several times myself: the materialized view is created successfully, but when testing the code, it simply doesn't refresh.


16) If the materialized view has no COUNT(*), then fast refresh is supported only on conventional DML inserts and direct loads.

The same phenomenon as in number 15:

rwijk@ORA11GR1> update myemp
  2     set sal = 5000
  3   where ename = 'KING'
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sum(sal) sumsal
  6       , count(sal) cntsal
  7    from myemp
  8   group by deptno
  9  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL     CNTSAL
---------- ---------- ----------
        30       9400          6
        20      10875          5
        10       8750          3

3 rows selected.

rwijk@ORA11GR1> update myemp
  2     set sal = 0
  3   where ename = 'KING'
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     SUMSAL     CNTSAL
---------- ---------- ----------
        30       9400          6
        20      10875          5
        10       8750          3

3 rows selected.

No error message and no refresh.


17) A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

Another example of no error message and no refresh:

rwijk@ORA11GR1> update myemp
  2     set sal = 5000
  3   where ename = 'KING'
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , max(sal) maxsal
  6       , count(sal) cntsal
  7       , count(*) cnt
  8    from myemp
  9   where sal > 1000
 10   group by deptno
 11  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     MAXSAL     CNTSAL        CNT
---------- ---------- ---------- ----------
        30       2850          5          5
        20       3000          4          4
        10       5000          3          3

3 rows selected.

rwijk@ORA11GR1> delete myemp
  2   where ename = 'KING'
  3  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

    DEPTNO     MAXSAL     CNTSAL        CNT
---------- ---------- ---------- ----------
        30       2850          5          5
        20       3000          4          4
        10       5000          3          3

3 rows selected.

18) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.

Deliberately forgetting the GROUPING or GROUPING_ID functions:

rwijk@ORA11GR1> drop table myemp purge
  2  /

Table dropped.

rwijk@ORA11GR1> create table myemp as select * from emp
  2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (deptno,sal) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sal
  6       , count(*) cnt
  7    from myemp
  8   group by cube(deptno,sal)
  9  /
  from myemp
       *
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

19) For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

Let's create a duplicate grouping set containing deptno.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , sal
  6       , grouping_id(deptno,sal)
  7       , count(*) cnt
  8    from myemp
  9   group by grouping sets(deptno,deptno,sal)
 10  /
  from myemp
       *
ERROR at line 8:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

20) If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.

I'm not sure how to test this one. It is not really a restriction, it just says that there is no limit on the number of joins and selected column if no outer join is used in the underlying query.


21) The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select e.deptno + d.deptno
  5       , count(*) cnt
  6    from myemp e
  7       , mydept d
  8   where e.deptno = d.deptno
  9   group by e.deptno
 10       , d.deptno
 11  /
  from myemp e
       *
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

22) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified.

First, let's remove department 10 and then create the MV with an outer join.

rwijk@ORA11GR1> delete mydept
  2   where deptno = 10
  3  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select e.deptno emp_deptno
  5       , d.deptno dept_deptno
  6       , count(*) cnt
  7    from myemp e
  8       , mydept d
  9   where e.deptno = d.deptno (+)
 10   group by e.deptno
 11       , d.deptno
 12  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10                      3
        30          30          6
        20          20          5

3 rows selected.

And now insert a record in the outer table.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
  2  values (7777,'VAN WIJK','JANITOR',500,10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10                      4
        30          30          6
        20          20          5

3 rows selected.

This works. But a modification on the inner table should not work:

rwijk@ORA11GR1> update mydept
  2     set deptno = 50
  3   where deptno = 20
  4  /

1 row updated.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10                      4
        30          30          6
        20          20          5

3 rows selected.

And indeed, it doesn't work. And we were not warned by an error message. And the view log is empty:

rwijk@ORA11GR1> select * from mlog$_mydept
  2  /

no rows selected

And this is how the materialized view should look like if it was allowed:

rwijk@ORA11GR1> select e.deptno emp_deptno
  2       , d.deptno dept_deptno
  3       , count(*) cnt
  4    from myemp e
  5       , mydept d
  6   where e.deptno = d.deptno (+)
  7   group by e.deptno
  8       , d.deptno
  9  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        20                      5
        10                      4
        30          30          6

3 rows selected.

23) Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided unique constraints exist on the join columns of the inner join table.

Let's drop the primary key of mydept.

rwijk@ORA11GR1> alter table mydept drop constraint mydept_pk
  2  /

Table altered.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10                      4
        30          30          6
        20          20          5

3 rows selected.

rwijk@ORA11GR1> delete myemp
  2   where empno = 7777
  3  /

1 row deleted.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10                      4
        30          30          6
        20          20          5

3 rows selected.

No change in the materialized view. The change is still pending in the materialized view log:

rwijk@ORA11GR1> select * from mlog$_myemp
  2  /

    DEPTNO        SAL
---------- ----------
M_ROW$$
--------------------------------------------------------------------------------
SNAPTIME$$          D O
------------------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
        10        500
AAATX0AAEAAAALWAAB
01-01-4000 00:00:00 D O
0000


1 row selected.

And the count for department 10 should now be 3, as can be seen by executing the query that's behind the materialized view:

rwijk@ORA11GR1> select e.deptno emp_deptno
  2       , d.deptno dept_deptno
  3       , count(*) cnt
  4    from myemp e
  5       , mydept d
  6   where e.deptno = d.deptno (+)
  7   group by e.deptno
  8       , d.deptno
  9  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        20                      5
        10                      3
        30          30          6

3 rows selected.

So no error message, but it just doesn't refresh.


24) If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

First recreate the primary key on mydept and then change the where clause from "e.deptno = d.deptno (+)" to "e.deptno between d.deptno (+) and 100". Functionally the same, as all deptno's are less than 100.

rwijk@ORA11GR1> alter table mydept add constraint mydept_pk primary key (deptno)
  2  /

Table altered.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select e.deptno emp_deptno
  5       , d.deptno dept_deptno
  6       , count(*) cnt
  7    from myemp e
  8       , mydept d
  9   where e.deptno between d.deptno (+) and 100
 10   group by e.deptno
 11       , d.deptno
 12  /
 where e.deptno between d.deptno (+) and 100
                                         *
ERROR at line 9:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

25) Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged.

Before showing the case where the view cannot be merged, first a case where the view can be merged:

rwijk@ORA11GR1> drop table mydept purge
  2  /

Table dropped.

rwijk@ORA11GR1> create table mydept as select * from dept
  2  /

Table created.

rwijk@ORA11GR1> alter table mydept add constraint mydept_pk primary key (deptno)
  2  /

Table altered.

rwijk@ORA11GR1> create materialized view log on mydept
  2    with rowid (deptno) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select e.deptno emp_deptno
  5       , d.deptno dept_deptno
  6       , count(*) cnt
  7    from myemp e
  8       , (select deptno from mydept) d
  9   where e.deptno = d.deptno
 10   group by e.deptno
 11       , d.deptno
 12  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10          10          3
        30          30          6
        20          20          5

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
  2  values (7777,'VAN WIJK','JANITOR',500,10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
  2  /

EMP_DEPTNO DEPT_DEPTNO        CNT
---------- ----------- ----------
        10          10          4
        30          30          6
        20          20          5

3 rows selected.

And now I add a dummy group by clause. It doesn't change the result of the query, but the view is not mergeable anymore.

rwijk@ORA11GR1> drop materialized view emp_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select e.deptno emp_deptno
  5       , d.deptno dept_deptno
  6       , count(*) cnt
  7    from myemp e
  8       , (select deptno from mydept group by deptno) d
  9   where e.deptno = d.deptno
 10   group by e.deptno
 11       , d.deptno
 12  /
  from myemp e
       *
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

26) It cannot contain a HAVING clause with a subquery.

rwijk@ORA11GR1> create materialized view emp_mv
  2    refresh fast on commit
  3  as
  4  select deptno
  5       , count(*) cnt
  6    from myemp
  7   group by deptno
  8  having avg(sal) > (select 1000 from dual)
  9  /
  from myemp
       *
ERROR at line 6:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

And with number 26, all documented restrictions on aggregate MV's are examined. Next up are union all MV's.

OTN Forums versus Stack Overflow

A quick intermezzo between the MV error related posts:

For almost three years now, I am answering questions on a regular basis at the OTN Discussion Forums, mainly at the SQL and PL/SQL Forum. By reading answers and giving answers, you are not only helping others, but you'll also find you are learning at greater speed than before. Most of the regulars like the forums very much of course, despite the sometimes annoying forum software, and especially upgrades of this software.

A couple of weeks ago, I started answering some Oracle related questions on Stack Overflow. And I'm impressed. Very impressed. The guys from Fog Creek Software (you may know the company because of the famous Joel On Software blog, written by its CEO Joel Spolsky) really know what technical forum software should look like. Every little thing that has annoyed me about the OTN forum software, has been addressed by Stack Overflow. A couple of examples:

Duplicate threads
OTN has different forums for each technology. But at times the poster of a question is not entirely sure what the best place for his question is. So he posts the same question in different forums. Visitors of only one forum may end up answering questions that have already been answered in the other forum. Stack Overflow works with tags. A single question can have several tags. People who answer questions, search for questions with specific tags. A simple but great answer for the duplicate thread problem.

Posting code
OTN somehow has problems posting code correctly. The unequal sign <> disappears. An outer join sign (+) transforms to some small graphical icon. And most important: newbies cannot easily find how to post code, leading to many unreadable questions. Stack Overflow has a single button to transform your code in, well, code. Without strange transformations.

Point system
OTN has a point system where the poster of the question can reward answers by marking them as Correct or Helpful. But it's not mandatory to do so. And sometimes plain wrong answers get marked as correct, degrading the quality of the post for future google searchers. It also gives an unsatisfying feeling when some very time consuming or great answers do not get rewarded, while some simple one liners do get the full 10 points. And it bothers me to watch regulars go as far as begging for a point reward; this surely cannot be your life's goal. Stack Overflow works with reputation. The more reputation you have, the more privileges you receive. Everyone can up vote (+10) or down vote (-2) an answer or comment, after they have achieved some reputation themselves. A very convenient way to let the poster of the question know which answer is regarded highly by their peers. Your reputation will be damaged if you post rubbish and/or DKB-style commercial "Here are my notes" answers. And future searches will find much clearer and more valuable answers.

And there are several other topics as well at which Stack Overflow outperforms OTN. When using the site, you feel it has been given good thought, as you can also see in this video where Joel Spolsky explains the concepts.

However, there is one thing at which OTN still excels: the knowledge of the people and therefore the quality of the answers. And ultimately, that is what counts. Most regulars at OTN give great answers and a few are not. For Oracle related questions at Stack Overflow, it's the other way around now. It's still rather good, but not excellent. So, for the best learning experience, I still recommend OTN. But I wish that the best of both worlds can be combined somewhere in the future.

PS: if you want a good laugh, then you should read this hilarious Stack Overflow thread about the user with the most reputation.

Fast refreshable materialized view errors, part two: join MV's

Previous post about simple MV's

In this second part I'll start with a fast refreshable join materialized view, based on the emp and dept table:

rwijk@ORA11GR1> create table myemp as select * from emp
 2  /

Table created.

rwijk@ORA11GR1> create table mydept as select * from dept
 2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
 2  /

Table altered.

rwijk@ORA11GR1> alter table mydept add primary key (deptno)
 2  /

Table altered.

rwijk@ORA11GR1> alter table myemp add foreign key (deptno) references mydept(deptno)
 2  /

Table altered.

Join MV's require materialized view logs with the rowid:

rwijk@ORA11GR1> create materialized view log on myemp
 2    with rowid
 3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view log on mydept
 2    with rowid
 3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view empdept_mv
 2    refresh fast on commit
 3  as
 4  select e.rowid e_rowid
 5       , d.rowid d_rowid
 6       , e.empno
 7       , e.ename
 8       , d.deptno
 9       , d.dname
10    from myemp e
11       , mydept d
12   where e.deptno = d.deptno
13     and d.deptno = 10
14  /

Materialized view created.

The join MV selects some attributes from both myemp and mydept, but only from department 10. This setup works:

rwijk@ORA11GR1> select * from empdept_mv
 2  /

E_ROWID            D_ROWID            EMPNO ENAME      DEPTNO DNAME
------------------ ------------------ ----- ---------- ------ --------------
AAAS3MAAEAAACJTAAG AAAS3NAAEAAACJbAAA  7782 CLARK          10 ACCOUNTING
AAAS3MAAEAAACJTAAI AAAS3NAAEAAACJbAAA  7839 KING           10 ACCOUNTING
AAAS3MAAEAAACJTAAN AAAS3NAAEAAACJbAAA  7934 MILLER         10 ACCOUNTING

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,deptno)
 2    values (7777,'VAN WIJK',10)
 3  /

1 row created.

rwijk@ORA11GR1> commit
 2  /

Commit complete.

rwijk@ORA11GR1> select * from empdept_mv
 2  /

E_ROWID            D_ROWID            EMPNO ENAME      DEPTNO DNAME
------------------ ------------------ ----- ---------- ------ --------------
AAAS3MAAEAAACJTAAG AAAS3NAAEAAACJbAAA  7782 CLARK          10 ACCOUNTING
AAAS3MAAEAAACJTAAI AAAS3NAAEAAACJbAAA  7839 KING           10 ACCOUNTING
AAAS3MAAEAAACJTAAN AAAS3NAAEAAACJbAAA  7934 MILLER         10 ACCOUNTING
AAAS3MAAEAAACJVAAA AAAS3NAAEAAACJbAAA  7777 VAN WIJK       10 ACCOUNTING

4 rows selected.

According to the documentation, the following restrictions apply for fast refreshable join MV's:

1. All restrictions from "General Restrictions on Fast Refresh".
2. Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
3. They cannot have GROUP BY clauses or aggregates.
4. Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
5. You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.

The first one is discussed in the previous post (see link at the top of this post)


2) Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

Here I'll omit the rowid of mydept:

rwijk@ORA11GR1> delete myemp where empno = 7777
  2  /

1 row deleted.

rwijk@ORA11GR1> drop materialized view empdept_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , e.empno
  6       , e.ename
  7       , d.deptno
  8       , d.dname
  9    from myemp e
 10       , mydept d
 11   where e.deptno = d.deptno
 12     and d.deptno = 10
 13  /
   and d.deptno = 10
                  *
ERROR at line 12:
ORA-12052: cannot fast refresh materialized view RWIJK.EMPDEPT_MV

We have seen the ORA-12052 a few times in the previous post, but this time it's because d.rowid is missing from the select list of the materialized view. For some reason, I usually forget adding the rowid when adding a table to the from-clause. So I am very familiar with this particular situation.


3) They cannot have GROUP BY clauses or aggregates.

This situation overlaps a bit with the next post's topic: aggregate MV's. Let's just add a dummy group by clause:

rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , d.rowid d_rowid
  6       , e.empno
  7       , e.ename
  8       , d.deptno
  9       , d.dname
 10    from myemp e
 11       , mydept d
 12   where e.deptno = d.deptno
 13     and d.deptno = 10
 14   group by e.rowid
 15       , d.rowid
 16       , e.empno
 17       , e.ename
 18       , d.deptno
 19       , d.dname
 20  /
   and d.deptno = 10
                  *
ERROR at line 13:
ORA-32401: materialized view log on "RWIJK"."MYDEPT" does not have new values

As we'll see in the next post as well, the materialized view logs for aggregate MV's need all columns and the "including new values" clause:

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> drop materialized view log on mydept
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid (empno,ename,deptno) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view log on mydept
  2    with rowid (deptno,dname) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , d.rowid d_rowid
  6       , e.empno
  7       , e.ename
  8       , d.deptno
  9       , d.dname
 10    from myemp e
 11       , mydept d
 12   where e.deptno = d.deptno
 13     and d.deptno = 10
 14   group by e.rowid
 15       , d.rowid
 16       , e.empno
 17       , e.ename
 18       , d.deptno
 19       , d.dname
 20  /

Materialized view created.

With the new materialized view logs in place, the MV is created successfully. And even better, the MV gets refreshed:

rwijk@ORA11GR1> select * from empdept_mv
  2  /

E_ROWID            D_ROWID            EMPNO ENAME      DEPTNO DNAME
------------------ ------------------ ----- ---------- ------ --------------
AAAS3MAAEAAACJTAAI AAAS3NAAEAAACJbAAA  7839 KING           10 ACCOUNTING
AAAS3MAAEAAACJTAAN AAAS3NAAEAAACJbAAA  7934 MILLER         10 ACCOUNTING
AAAS3MAAEAAACJTAAG AAAS3NAAEAAACJbAAA  7782 CLARK          10 ACCOUNTING

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,deptno)
  2    values (7777,'VAN WIJK',10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from empdept_mv
  2  /

E_ROWID            D_ROWID            EMPNO ENAME      DEPTNO DNAME
------------------ ------------------ ----- ---------- ------ --------------
AAAS3MAAEAAACJTAAI AAAS3NAAEAAACJbAAA  7839 KING           10 ACCOUNTING
AAAS3MAAEAAACJTAAN AAAS3NAAEAAACJbAAA  7934 MILLER         10 ACCOUNTING
AAAS3MAAEAAACJTAAG AAAS3NAAEAAACJbAAA  7782 CLARK          10 ACCOUNTING
AAAS3MAAEAAACJVAAB AAAS3NAAEAAACJbAAA  7777 VAN WIJK       10 ACCOUNTING

4 rows selected.

But then why is it mentioned as a restriction? Let's try again, but with an extra aggregate function in the select list. Maybe this will break it?

rwijk@ORA11GR1> delete myemp where empno = 7777
  2  /

1 row deleted.

rwijk@ORA11GR1> drop materialized view empdept_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , d.rowid d_rowid
  6       , e.empno
  7       , e.ename
  8       , d.deptno
  9       , d.dname
 10       , count(*) cnt
 11    from myemp e
 12       , mydept d
 13   where e.deptno = d.deptno
 14     and d.deptno = 10
 15   group by e.rowid
 16       , d.rowid
 17       , e.empno
 18       , e.ename
 19       , d.deptno
 20       , d.dname
 21  /

Materialized view created.

rwijk@ORA11GR1> select * from empdept_mv
  2  /

E_ROWID            D_ROWID            EMPNO ENAME      DEPTNO DNAME          CNT
------------------ ------------------ ----- ---------- ------ -------------- ---
AAAS3MAAEAAACJTAAI AAAS3NAAEAAACJbAAA  7839 KING           10 ACCOUNTING       1
AAAS3MAAEAAACJTAAN AAAS3NAAEAAACJbAAA  7934 MILLER         10 ACCOUNTING       1
AAAS3MAAEAAACJTAAG AAAS3NAAEAAACJbAAA  7782 CLARK          10 ACCOUNTING       1

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,deptno)
  2    values (7777,'VAN WIJK',10)
  3  /

1 row created.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> select * from empdept_mv
  2  /

E_ROWID            D_ROWID            EMPNO ENAME      DEPTNO DNAME          CNT
------------------ ------------------ ----- ---------- ------ -------------- ---
AAAS3MAAEAAACJTAAI AAAS3NAAEAAACJbAAA  7839 KING           10 ACCOUNTING       1
AAAS3MAAEAAACJTAAN AAAS3NAAEAAACJbAAA  7934 MILLER         10 ACCOUNTING       1
AAAS3MAAEAAACJTAAG AAAS3NAAEAAACJbAAA  7782 CLARK          10 ACCOUNTING       1
AAAS3MAAEAAACJVAAA AAAS3NAAEAAACJbAAA  7777 VAN WIJK       10 ACCOUNTING       1

4 rows selected.

And again it works. This restriction doesn't seem to be a restriction. But maybe it breaks when adding some more complexity. I certainly wouldn't base a production system on it, after having been warned by the documentation.


4) Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

First restore the myemp materialized view log and then drop the mydept view log:

rwijk@ORA11GR1> drop materialized view log on myemp
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> create materialized view log on myemp
  2    with rowid
  3  /

Materialized view log created.

rwijk@ORA11GR1> drop materialized view log on mydept
  2  /

Materialized view log dropped.

rwijk@ORA11GR1> drop materialized view empdept_mv
  2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , d.rowid d_rowid
  6       , e.empno
  7       , e.ename
  8       , d.deptno
  9       , d.dname
 10    from myemp e
 11       , mydept d
 12   where e.deptno = d.deptno
 13     and d.deptno = 10
 14  /
   and d.deptno = 10
                  *
ERROR at line 13:
ORA-23413: table "RWIJK"."MYDEPT" does not have a materialized view log

This error message couldn't be better!


5) You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.

rwijk@ORA11GR1> create materialized view log on mydept
  2    with rowid
  3  /

Materialized view log created.

rwijk@ORA11GR1> create type to_emp is object
  2  ( empno number(4)
  3  , ename varchar2(10)
  4  );
  5  /

Type created.

rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , d.rowid d_rowid
  6       , to_emp
  7         ( e.empno
  8         , e.ename
  9         ) emp_object
 10       , d.deptno
 11       , d.dname
 12    from myemp e
 13       , mydept d
 14   where e.deptno = d.deptno
 15     and d.deptno = 10
 16  /
  from myemp e
       *
ERROR at line 12:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Hmm, we have seen this one before.

Next up are the fast refreshable aggregate MV's.

---

UPDATE

6) An extra undocumented restriction (see first comment by Timur Akhmadeev): the materialized view cannot contain an ANSI join.

rwijk@ORA11GR1> drop materialized view empdept_mv
  2  /
drop materialized view empdept_mv
*
ERROR at line 1:
ORA-12003: materialized view "RWIJK"."EMPDEPT_MV" does not exist


rwijk@ORA11GR1> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select e.rowid e_rowid
  5       , d.rowid d_rowid
  6       , e.empno
  7       , e.ename
  8       , d.deptno
  9       , d.dname
 10    from myemp e
 11         inner join mydept d on (e.deptno = d.deptno)
 12   where d.deptno = 10
 13  /
 where d.deptno = 10
                  *
ERROR at line 12:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

And this situation also leads to ORA-12054. Thanks Timur.

Fast refreshable materialized view errors, part one

Never put more than one error condition into one error message. It's just so common sense, I didn't even put it in the latest version of Standards and Guidelines for application developers anymore. Maybe wrongly so, because I see violations of this rule a little too often.

An example of what I mean: we have a company mail server that requires us to change our passwords every month. And when entering a new password, several conditions are checked, but they all lead to one error message: "Invalid password". You are left guessing what exactly was wrong with your last entered new password. Only by experiment you can become aware of the several error conditions, like:

• Password must be more than six characters long.
• Password must contain at least one upper case character.
• Password must contain at least one lower case character.
• Password must contain at least one special character.
• Password must be different than your last six passwords.

Of course it would be very convenient if one of the above five sentences appeared as the error message. With only a little extra effort, you can make your code much more user friendly. Just introduce an error message per error condition.

Now if only Oracle did this in their fast (read: incremental) refreshable materialized view functionality, my working life for the last two months would have been so much easier. Fortunately, the documentation about materialized views is quite good, so this compensates a little.

There was also a second annoyance. I encountered situations where I did not comply with all documented restrictions. I wasn't told that I did something wrong: the materialized view was successfully created. Only when testing the software, it just didn't work. No error message, just the on commit refresh MV didn't refresh at all.

I could rant much more about this. In fact, people in my vicinity know I already did quite a lot lately. But instead I've decided to do something a little more constructive: write a few blog posts explaining possible causes for fast refresh MV related error messages. I am setting up a situation violating one of the restrictions and show the error message, if any. This way I can collect all situations where a same error message is given, and then give all situations for each error message.

In this first post I'll address some general MV error conditions. In later posts I will address issues with join MV's, aggregate MV's, union all MV's and nested MV's. After that I have a post planned summarizing the error messages and possible causes, the other way around. Hopefully people coming here by google will find some solution for solving their MV related errors. I'll end the series with a post about performance of fast refreshable MV's.

So the rest of this post I'll discuss the general restrictions for fast refresh. According to the Oracle documentation, they are:

1. The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
2. The materialized view must not contain references to RAW or LONG RAW data types.
3. It cannot contain a SELECT list subquery.
4. It cannot contain analytical functions (for example, RANK) in the SELECT clause.
5. It cannot contain a MODEL clause.
6. It cannot contain a HAVING clause with a subquery.
7. It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
8. It cannot contain a [START WITH …] CONNECT BY clause.
9. It cannot contain multiple detail tables at different sites.
10. ON COMMIT materialized views cannot have remote detail tables.
11. Nested materialized views must have a join or aggregate.

I tested all, except number 6, which I'll address when discussing aggregate MV errors, number 9 because I don't have that many databases here on my laptop, and number 11, which I'll postpone when discussing nested MV's.

First a simple materialized view, no joins, aggregates or union alls, on a table which is an exact copy of the EMP table:

rwijk@ORA11GR1> create table myemp as select * from emp
 2  /

Table created.

rwijk@ORA11GR1> alter table myemp add primary key (empno)
 2  /

Table altered.

Simple fast refreshable MV's require a materialized view log on the base table with the primary key:

rwijk@ORA11GR1> create materialized view log on myemp
 2    with primary key
 3  /

Materialized view log created.

A simple fast refreshable materialized view containing the employees of department 10:

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9    from myemp
10   where deptno = 10
11  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
 2  /

    EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
     7782 CLARK      MANAGER         2450         10
     7839 KING       PRESIDENT       5000         10
     7934 MILLER     CLERK           1300         10

3 rows selected.

Let's verify that the on commit refresh works:

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
 2    values (7777,'VAN WIJK','JANITOR',500,10)
 3  /

1 row created.

rwijk@ORA11GR1> commit
 2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
 2  /

    EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
     7782 CLARK      MANAGER         2450         10
     7839 KING       PRESIDENT       5000         10
     7934 MILLER     CLERK           1300         10
     7777 VAN WIJK   JANITOR          500         10

4 rows selected.

It works. This is the base setup. The restrictions are tested as a variant on this scenario.


1) The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

First restore the situation and then create an MV with sysdate:

rwijk@ORA11GR1> delete myemp where empno = 7777
 2  /

1 row deleted.

rwijk@ORA11GR1> drop materialized view emp_mv
 2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9       , sysdate now
10    from myemp
11   where deptno = 10
12  /
 from myemp
      *
ERROR at line 10:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

A perfect example of a not-so-clear error message. Why not tell "The materialized view cannot contain SYSDATE" or something like that?


2) The materialized view must not contain references to RAW or LONG RAW data types.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9       , utl_raw.cast_to_raw('a') rawcol
10    from myemp
11   where deptno = 10
12  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
 2  /

    EMPNO ENAME      JOB              SAL     DEPTNO RAWCOL
---------- ---------- --------- ---------- ---------- ----------
     7782 CLARK      MANAGER         2450         10 61
     7839 KING       PRESIDENT       5000         10 61
     7934 MILLER     CLERK           1300         10 61

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno)
 2    values (7777,'VAN WIJK','JANITOR',500,10)
 3  /

1 row created.

rwijk@ORA11GR1> commit
 2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
 2  /

    EMPNO ENAME      JOB              SAL     DEPTNO RAWCOL
---------- ---------- --------- ---------- ---------- ----------
     7782 CLARK      MANAGER         2450         10 61
     7839 KING       PRESIDENT       5000         10 61
     7934 MILLER     CLERK           1300         10 61
     7777 VAN WIJK   JANITOR          500         10 61

4 rows selected.

Hmm, this restriction isn't a restriction. Maybe Oracle means that it's not possible to refer a RAW column instead of a RAW expression:

rwijk@ORA11GR1> delete myemp where empno = 7777
 2  /

1 row deleted.

rwijk@ORA11GR1> alter table myemp add (rawcol raw(16))
 2  /

Table altered.

rwijk@ORA11GR1> drop materialized view emp_mv
 2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9       , rawcol
10    from myemp
11   where deptno = 10
12  /

Materialized view created.

rwijk@ORA11GR1> select * from emp_mv
 2  /

    EMPNO ENAME      JOB              SAL     DEPTNO RAWCOL
---------- ---------- --------- ---------- ---------- ----------
     7782 CLARK      MANAGER         2450         10
     7839 KING       PRESIDENT       5000         10
     7934 MILLER     CLERK           1300         10

3 rows selected.

rwijk@ORA11GR1> insert into myemp (empno,ename,job,sal,deptno,rawcol)
 2    values (7777,'VAN WIJK','JANITOR',500,10,utl_raw.cast_to_raw('a'))
 3  /

1 row created.

rwijk@ORA11GR1> commit
 2  /

Commit complete.

rwijk@ORA11GR1> select * from emp_mv
 2  /

    EMPNO ENAME      JOB              SAL     DEPTNO RAWCOL
---------- ---------- --------- ---------- ---------- ----------
     7782 CLARK      MANAGER         2450         10
     7839 KING       PRESIDENT       5000         10
     7934 MILLER     CLERK           1300         10
     7777 VAN WIJK   JANITOR          500         10 61

4 rows selected.

Again, it works. This restriction isn't a restriction at all...


3) It cannot contain a SELECT list subquery.

rwijk@ORA11GR1> delete myemp where empno = 7777
 2  /

1 row deleted.

rwijk@ORA11GR1> drop materialized view emp_mv
 2  /

Materialized view dropped.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9       , (select emp.ename from emp where emp.empno = myemp.mgr) manager_name
10    from myemp
11   where deptno = 10
12  /
    , (select emp.ename from emp where emp.empno = myemp.mgr) manager_name
                                                              *
ERROR at line 9:
ORA-22818: subquery expressions not allowed here

That's more like it. An error message that is spot on for select list subqueries.


4) It cannot contain analytical functions (for example, RANK) in the SELECT clause.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9       , row_number() over (order by empno) rn
10    from myemp
11   where deptno = 10
12  /
where deptno = 10
               *
ERROR at line 11:
ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV

Not very helpful ...


5) It cannot contain a MODEL clause.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9    from myemp
10   where deptno = 10
11   model
12         dimension by (empno)
13         measures (ename,job,sal,deptno,0 copysal)
14         ( copysal[any] = sal[cv()]
15         )
16  /
where deptno = 10
               *
ERROR at line 10:
ORA-12052: cannot fast refresh materialized view RWIJK.EMP_MV

Again...


7) It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4  select empno
 5       , ename
 6       , job
 7       , sal
 8       , deptno
 9    from myemp
10   where deptno = 10
11     and not exists
12         ( select 'a manager'
13             from emp e2
14            where e2.empno = myemp.mgr
15         )
16  /
where deptno = 10
               *
ERROR at line 10:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

And again ...
Note that whether ORA-12052 or ORA-12054 appears, looks more dependent on some internal Oracle code path, than on what the user is doing wrong.


8) It cannot contain a [START WITH …] CONNECT BY clause.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4   select empno
 5        , ename
 6        , job
 7        , sal
 8        , deptno
 9     from myemp
10    where deptno = 10
11  connect by prior empno = mgr
12    start with mgr is null
13  /
 where deptno = 10
                *
ERROR at line 10:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Sigh...


10) ON COMMIT materialized views cannot have remote detail tables.

For this one I have started my 10.2.0.1 instance and created a database link "ora10" to this database.

rwijk@ORA11GR1> create database link ora10
 2    connect to rwijk
 3    identified by rwijk
 4    using 'ora10gr2'
 5  /

Database link created.

rwijk@ORA11GR1> select * from v$version@ora10 where rownum = 1
 2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

1 row selected.

rwijk@ORA11GR1> create materialized view emp_mv
 2    refresh fast on commit
 3  as
 4   select e.empno
 5        , e.ename
 6        , e.job
 7        , e.sal
 8        , e.deptno
 9        , d.dname
10     from myemp e
11        , dept@ora10 d
12    where e.deptno = d.deptno
13      and e.deptno = 10
14  /
   and e.deptno = 10
                  *
ERROR at line 13:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

And again, the not very helpful ORA-12054.

This completes the simple fast refreshable MV error conditions. Next up are the fast refreshable join MV's.

Fast refreshable materialized view errors, part two: join MV's

Third Planboard DBA Symposium

Yesterday I attended the third version of the Planboard DBA Symposium. Ten presentations were given in two parallel tracks. Some presentations were a little too "hardcore DBA" for me as a simple developer, but still there were several ones I really liked. My highlights were Toon's presentation "APEX for DBA's" and Harald's presentation about SQL Plan Management. Both succeeded in making their subject seem perfectly simple. A sign that the presentations were well delivered.

Toon Koppelaars managed to give some great insights about when processes are executed during navigation in APEX. And his demo contained a nice example of how to have checkboxes in front of your rows and have your code only process the checked ones. I love APEX for its power and simplicity, but Toon's presentation also made it very clear that APEX is lacking a solid Concepts Manual. Let's hope Oracle picks up this signal before introducing yet another great GUI feature.

Harald van Breederode presented SQL Plan Management by first explaining several predecessors, like outlines and SQL Profiles and used some great SQL*Plus demos to show how SQL Plan Management works and a very nice one showing that even the Rule Based Optimizer is not always predictable. A great use for SQL Plan Management is when upgrading your database. Set the OPTIMIZER_FEATURE_ENABLE parameter to the old version, capture the plans into the SQL Management Base and then switch the parameter to the new version. Now you have the possibility to only evolve plans that are equal or better than the old plans. If a scenario like this would have been possible when my current client upgraded from 9 to 10, then the upgrade project would have been WAY shorter and cheaper ...

The symposium was very well organized. And the location was perfect too: VX Company in Baarn has a nice building with some great rooms for presentations. I sure hope I will be visiting this symposium, or its developer related one, again in the future.

My own presentation and the accompanying scripts can be downloaded below. It is in Dutch, although I think you'll be able to understand most of it, even when you don't know the language.

Powerpoint-presentatie Alles Over Groeperen

Bijbehorende demonstratiescripts

We have a winner

Well, I'm not the judge and I don't know what other solutions will be sent in, but I guess it's hard to beat this magnificent O(NlogN) solution to the First International NoCOUG SQL Challenge by Alberto Dell'Era, posted here on his website. If you have a statistics or mathematics background, you might actually be able to understand it completely. If not or if you forgot most of it, like me, then just watch and enjoy. Who said SQL is not complete? :-)

Hats off and a deep bow to you, Alberto.

Choosing between SQL and PL/SQL

If you prefer to read in German, then you can find a translation here.


On my post Calculating probabilities with N throws of a die, I received a comment by Narendra saying:

I hope you are not serious about your last statement with some comments I'm sure it's not that hard to maintain....:)

But I was serious. However, it's a sentiment I hear a lot: after my presentation Do more with SQL I received similar comments claiming PL/SQL would be easier in some cases. And according to Iggy Fernandez, Steven Feuerstein has said here:

Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.

And I don't get it.

Well, I can understand why that would be a first reaction when seeing some undocumented long piece of SQL containing some of the newer SQL constructs. But when thinking a bit longer about the subject, I don't think it's a totally fair reaction. Here's why.

1. When faced with a challenging problem, most people tend to resort to the language they are most comfortable with. I know I do. For example, I'm way better with PL/SQL than I am with Java. So when faced with a hard algorithm, I'll always use PL/SQL. And I bet a Java programmer reasons the other way round. So when saying that straight SQL is harder to maintain than PL/SQL, I guess you are really saying that your PL/SQL skills are very good, but your SQL skills are, well, somewhat less than very good. That's no problem at all, since you will still be able to build applications effectively. But I don't think the language itself is to blame, it's the skills of the people talking that language.



2. In production code, I see PL/SQL code more often being documented with comments than SQL code. But every piece of production code that isn't straightforward should be documented. Why does SQL code rarely contain comments? Probably because most SQL statements in production are of the basic SELECT ... FROM ... WHERE kind. And when used to not commenting those easy SQL statements, the harder ones are almost automatically lacking comments as well. When you start adding comments for SQL code, as well as for your PL/SQL code, then it will be a reason less for why you may find SQL code harder to grasp.



3. In PL/SQL you are able to split up a complex task into many simple tasks. Each task is then performed by a single function or a procedure. And those functions and procedures have clear names, making the code self documenting. This is called modularizing your code and you've probably been taught about this subject in school already. Complex SQL used to be just a giant piece of text lacking this ability, but from Oracle9i onwards, Oracle gave us the WITH-clause (also known as subquery factoring). With this clause you can give a meaningful name to each subpiece of SQL. Thus, the same kind of modularization we achieve with PL/SQL, is possible with SQL since 9i as well.

And so I think SQL is equally readable, just a little more compact. And often faster due to less context switching. And more often correct because it's one read consistent query instead of several queries taking place at different times.

An example using the code from Calculating probabilities with N throws of a die, of how it could look like in production. Difference is that the results of that query are inserted into a table. I made both variants "production like" by documenting them well. First a package using SQL:

rwijk@ORA11GR1> create package probabilities_sql
  2  as
  3    --
  4    -- The procedure "calculate" calculates all probabilities with
  5    -- p_number_of_dies throws of a die. The results of the calculation
  6    -- are inserted into the table probabilities.
  7    --
  8    procedure calculate (p_number_of_throws in number)
  9    ;
 10  end probabilities_sql;
 11  /

Package is aangemaakt.

rwijk@ORA11GR1> create package body probabilities_sql
  2  as
  3    procedure calculate (p_number_of_throws in number)
  4    is
  5    begin
  6      insert into probabilities
  7      ( sum_of_dies
  8      , percentage
  9      )
 10      with number_of_die_faces as (select count(*) cnt from die)
 11      , all_probabilities as
 12      ( select sum_value
 13             , prob
 14             , i
 15          from --
 16               -- Generate as many rows as there are possible combinations of the
 17               -- dies. This equals: power(,p_number_of_throws).
 18               -- For example: with a traditional die (6 faces) and 3 throws, there
 19               -- are power(6,3) = 216 rows with a l-value running from 1 until 216.
 20               --
 21               ( select level l
 22                   from number_of_die_faces
 23                connect by level <= power(cnt,p_number_of_throws)
 24               )
 25             , number_of_die_faces
 26         model
 27               --
 28               -- A reference model to be able to quickly lookup the face_value
 29               -- and probability when provided a face_id
 30               --
 31               reference r on (select face_id, face_value, probability from die)
 32                 dimension by (face_id)
 33                 measures (face_value,probability)
 34               main m
 35                 --
 36                 -- Each combination is in a different partition.
 37                 -- Which means it is easy to parallellize if necessary.
 38                 --
 39                 partition by (l rn, cnt)
 40                 dimension by (0 i)
 41                 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
 42               --
 43               -- Iterate as many times as there are throws of the die.
 44               --
 45               rules iterate (1000) until (iteration_number+1=p_number_of_throws)
 46               --
 47               -- For each throw of the die, calculate the face_id, remainder, the
 48               -- sum and probability. For the sum and probability, the reference
 49               -- model is used as a lookup. Each iteration overwrites the previous
 50               -- one.
 51               --
 52               ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
 53               , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
 54               , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
 55               , prob[0]        = prob[0] * probability[die_face_id[0]]
 56               )
 57      )
 58      --
 59      -- All probabilities of each possible combination are now calculated.
 60      -- Now, sum them all up per sum of all face_values.
 61      --
 62      select sum_value
 63           , sum(prob)
 64        from all_probabilities
 65       group by sum_value
 66      ;
 67    end calculate;
 68  end probabilities_sql;
 69  /

Package-body is aangemaakt.

And a package doing it the PL/SQL way, using the same idea:

rwijk@ORA11GR1> create package probabilities_plsql
  2  as
  3    --
  4    -- The procedure "calculate" calculates all probabilities with
  5    -- p_number_of_dies throws of a die. The results of the calculation
  6    -- are inserted into the table probabilities.
  7    --
  8    procedure calculate (p_number_of_throws in number)
  9    ;
 10  end probabilities_plsql;
 11  /

Package is aangemaakt.

rwijk@ORA11GR1> create package body probabilities_plsql
  2  as
  3    g_number_of_die_faces number(4)
  4    ;
  5    procedure initialization
  6    --
  7    -- Calculate the number of die faces (6 in case of a traditional die) only once.
  8    --
  9    is
 10    begin
 11      select count(*)
 12        into g_number_of_die_faces
 13        from die
 14      ;
 15    end initialization
 16    ;
 17    function face_value
 18    ( p_face_id in die.face_id%type
 19    ) return die.face_value%type result_cache relies_on (die)
 20    --
 21    -- A lookup function returning the face_value of a given face_id.
 22    -- This function is called multiple times for the same face_id's and
 23    -- is therefore optimized by the result_cache hint.
 24    --
 25    is
 26      l_face_value die.face_value%type;
 27    begin
 28      select face_value
 29        into l_face_value
 30        from die
 31       where face_id = p_face_id
 32      ;
 33      return l_face_value;
 34    end face_value
 35    ;
 36    function probability
 37    ( p_face_id in die.face_id%type
 38    ) return die.probability%type result_cache relies_on (die)
 39    --
 40    -- A lookup function returning the probability of a given face_id.
 41    -- This function is called multiple times for the same face_id's and
 42    -- is therefore optimized by the result_cache hint.
 43    --
 44    is
 45      l_probability die.probability%type;
 46    begin
 47      select probability
 48        into l_probability
 49        from die
 50       where face_id = p_face_id
 51      ;
 52      return l_probability;
 53    end probability
 54    ;
 55    procedure calculate (p_number_of_throws in number)
 56    is
 57      l_die_face_id die.face_id%type;
 58      l_remainder   number(10);
 59      l_sum         probabilities.sum_of_dies%type;
 60      l_probability probabilities.percentage%type
 61      ;
 62      type ta_probabilities is table of probabilities%rowtype index by pls_integer;
 63      a_probabilities ta_probabilities;
 64    begin
 65      --
 66      -- Loop as many times as there are possible combinations of the
 67      -- dies. This number equals: power(,p_number_of_throws).
 68      -- For example: with a traditional die (6 faces) and 3 throws, there
 69      -- are power(6,3) = 216 iterations.
 70      --
 71      for i in 1 .. power(g_number_of_die_faces,p_number_of_throws)
 72      loop
 73        l_remainder := i;
 74        l_sum := 0;
 75        l_probability := 1;
 76        --
 77        -- For each combination, iterate over all throws of each individual die,
 78        -- and calculate the face_id of that die (using l_die_face_id and
 79        -- l_remainder) and use that face_id to calculate the sum of the die
 80        -- face values and the probability.
 81        --
 82        for j in 1 .. p_number_of_throws
 83        loop
 84          l_die_face_id := 1 + mod(l_remainder-1, g_number_of_die_faces);
 85          l_remainder   := ceil((l_remainder-l_die_face_id+1)/g_number_of_die_faces);
 86          l_sum         := l_sum + face_value(l_die_face_id);
 87          l_probability := l_probability * probability(l_die_face_id);
 88        end loop;
 89        --
 90        -- Sum up all the probabilities with the same sum.
 91        --
 92        a_probabilities(l_sum).sum_of_dies := l_sum;
 93        a_probabilities(l_sum).percentage :=
 94          nvl(a_probabilities(l_sum).percentage,0) + l_probability
 95        ;
 96      end loop;
 97      --
 98      -- Bulk insert all calculated probabilities into the table PROBABILIIES.
 99      --
100      forall i in indices of a_probabilities
101        insert into probabilities
102        values a_probabilities(i)
103      ;
104    end calculate
105    ;
106  begin
107    initialization;
108  end probabilities_plsql;
109  /

Package-body is aangemaakt.

Note that this algorithm is not data intensive and the PL/SQL variant here is actually faster than the SQL variant due to 11g's result cache, but that's not the point here. The point is of course readability. Do you really think the SQL variant is much more complex than the PL/SQL variant? I'd love to hear your thoughts about this subject, whether you agree or not.

---

UPDATE

Three nice follow-up blog entries:

by Chen Shapira

by Laurent Schneider

by H.Tonguç Yılmaz