Materialized Views in Oracle

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

cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:

SQL> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select a.rowid dept_rowid
  5       , b.rowid emp_rowid
  6       , a.deptno
  7    from dept a
  8       , emp b
  9   where a.deptno = b.deptno
 10  /

Materialized view created.

the materialized view logs for aggregate MV's need all columns and the "including new values" clause:

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.