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