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