Fast refreshable materialized view

Fast refreshable join materialized view:

sql>  create table myemp as select * from emp
 2  /

Table created.

sql>  create table mydept as select * from dept
 2  /

Table created.

sql>  alter table myemp add primary key (empno)
 2  /

Table altered.

sql>  alter table mydept add primary key (deptno)
 2  /

Table altered.

sql>  alter table myemp add foreign key (deptno) references mydept(deptno)
 2  /

Table altered.

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

sql>  create materialized view log on myemp
 2    with rowid
 3  /

Materialized view log created.

sql>  create materialized view log on mydept
 2    with rowid
 3  /

Materialized view log created.

sql>  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:

sql>  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.

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

1 row created.

sql>  commit
 2  /

Commit complete.

sql>  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:

sql>  delete myemp where empno = 7777
  2  /

1 row deleted.

sql>  drop materialized view empdept_mv
  2  /

Materialized view dropped.

sql>  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:

sql>  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:

sql>  drop materialized view log on myemp
  2  /

Materialized view log dropped.

sql>  drop materialized view log on mydept
  2  /

Materialized view log dropped.

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

Materialized view log created.

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

Materialized view log created.

sql>  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:

sql>  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.

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

1 row created.

sql>  commit
  2  /

Commit complete.

sql>  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?

sql>  delete myemp where empno = 7777
  2  /

1 row deleted.

sql>  drop materialized view empdept_mv
  2  /

Materialized view dropped.

sql>  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.

sql>  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.

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

1 row created.

sql>  commit
  2  /

Commit complete.

sql>  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:

sql>  drop materialized view log on myemp
  2  /

Materialized view log dropped.

sql>  create materialized view log on myemp
  2    with rowid
  3  /

Materialized view log created.

sql>  drop materialized view log on mydept
  2  /

Materialized view log dropped.

sql>  drop materialized view empdept_mv
  2  /

Materialized view dropped.

sql>  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.

sql>  create materialized view log on mydept
  2    with rowid
  3  /

Materialized view log created.

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

Type created.

sql>  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.

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

sql>  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


sql>  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. 

ref:http://rwijk.blogspot.com/2009/05/fast-refreshable-materialized-view_31.html