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.