About Oracle: 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:

All restrictions from "General Restrictions on Fast Refresh".
Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
They cannot have GROUP BY clauses or aggregates.
Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
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.

10 comments:

Timur AkhmadeevJune 1, 2009 at 9:10 AM
Hi, Rob.

My 2ct:

1) They cannot contain ANSI join, details are here2) They cannot reference a table with a LOB column (even if it's not referenced in a query). See Bug 7120659.
Rob van WijkJune 1, 2009 at 11:05 PM
Hi Timur,

Thanks!

Only difference with the above mentioned 5 restrictions, is the fact that yours are undocumented restrictions (read: bugs). And so they should work and maybe they will in some future version/patch set.

The ANSI join one looks very common though, and I've updated the post to reflect this situation as well.

Regards,
Rob.
David WeigelJune 2, 2009 at 5:03 PM
I hate hate hate the no-ANSI-join restriction. I haven't done a "t1, t2 where t1.a=t2.a" join in years and I just Don't Like Them.
AnonymousJune 5, 2009 at 3:31 PM
Wonderful article!

Oracle's documentation (10gR2) for "plain vanilla" materialized views is indeed excellent, but as you've mentioned, once you try to do anything elaborate, the documents start becoming convoluted and even self-contradictory. (One page will say that you can't perform a Fast Refresh if the mview's defining query contains a UNION ALL, another page will say that you can, but only if A, B, and C are true...)

Oracle's debugging tools (e.g. DBMS_MVIEW.EXPLAIN_MVIEW) are really well-done, but also fall short when you move from simplicity to complexity.

The worst part of the whole situation is that it's difficult to tell from the documentation whether the process you're trying to implement is even supported. You have to poke, prod, and tweak various settings until you hit that nirvana-like state where Oracle finally gives up and says "Okay, I'll let you Fast Refresh now".

The whole process is fun, after a fashion, but it makes one wonder whether they're nursing a subconscious masochistic streak...
Gustavo LeigJune 11, 2009 at 4:52 AM
Hi, great article but I have one question...
my table is like

id number(9),
initial_date date

if I need to have the count(*) by month to_char(date,'mm/yyyy')
can I use a refresh fast mv?
Im getting an error ORA-12033 when creating the mv.

what should I do? Thanks in adv.
Rob van WijkJune 11, 2009 at 7:46 AM
Hi Gustavo,

> What should I do?

Well, the best place for such a question, is an Oracle forum.

I'll make an exception now:

SQL> create table t (id,initial_date)
2 as
3 select 1, date '2009-05-01' from dual union all
4 select 2, date '2009-05-20' from dual union all
5 select 3, date '2009-06-06' from dual
6 /

Tabel is aangemaakt.

SQL> create materialized view log on t
2 with rowid (initial_date) including new values
3 /

Gematerialiseerde viewlog is aangemaakt.

SQL> create materialized view mv
2 refresh fast on commit
3 as
4 select trunc(initial_date,'mm') initial_month
5 , count(*) cnt
6 from t
7 group by trunc(initial_date,'mm')
8 /

Gematerialiseerde view is aangemaakt.

SQL> select * from mv
2 /

INITIAL_MONTH CNT
------------------- --------------------------------------
01-05-2009 00:00:00 2
01-06-2009 00:00:00 1

2 rijen zijn geselecteerd.

SQL> insert into t values (4, date '2009-05-31')
2 /

1 rij is aangemaakt.

SQL> commit
2 /

Commit is voltooid.

SQL> select * from mv
2 /

INITIAL_MONTH CNT
------------------- --------------------------------------
01-05-2009 00:00:00 3
01-06-2009 00:00:00 1

2 rijen zijn geselecteerd.

Regards,
Rob.
AnonymousAugust 24, 2009 at 6:28 PM
Fantastic post. Why cannot Oracle explain in this nice fashion.
Patrick BaconMarch 5, 2013 at 10:33 PM
Thank you. This is concise.
Database TipsJanuary 3, 2015 at 9:39 AM
I had followed all the above restriction while creating Materialized view with refresh fast on COMMIT option, but still getting same error message ORA-12054: cannot set the on commit refresh attribute for the materialized view

CREATE MATERIALIZED VIEW log ON EventDiscount WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON TariffHasRatingTariff WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON TariffElementDiscount WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON TariffElementAttrDetails WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON RevenueCode WITH ROWID ;

create materialized view Vzw_ProdTariffDiscountInfo1
REFRESH FAST ON COMMIT
as
select
ted.product_id,thrt.tariff_id,thrt.rating_tariff_id,ed.event_discount_id,ed.discount_name,ed.assessment_event_type_id,
ed.dynamic_discount_boo,ed.otc_id,ed.pro_rating_boo,ed.discount_type,
ed.priority_num,ed.reset_counter_boo,ed.store_qualifying_usage_boo,
tead.product_price_attr_id,tead.one_off_attr_value,nvl(ed.revenue_code_id,-1),rc.revenue_code_name,
ed.ROWID as ed_ROWID,thrt.ROWID as thrt_ROWID,ted.ROWID as ted_ROWID,tead.ROWID as tead_ROWID,rc.ROWID as rc_ROWID
from EventDiscount ed, TariffHasRatingTariff thrt,TariffElementDiscount ted,TariffElementAttrDetails tead,RevenueCode rc
where
thrt.catalogue_change_id = (select catalogue_change_id from cataloguechange where catalogue_status=3)
and ed.catalogue_change_id = thrt.catalogue_change_id(+)
and ted.catalogue_change_id = thrt.catalogue_change_id
and ted.event_discount_id = ed.event_discount_id
and ted.tariff_id = thrt.tariff_id
and ted.start_dat <= sysdate
and (ted.end_dat is null or ted.end_dat >sysdate)
and tead.product_id = ted.product_id
and tead.tariff_id = thrt.tariff_id
and tead.start_dat < sysdate
and tead.catalogue_change_id = thrt.catalogue_change_id
and (tead.end_dat is null or tead.end_dat >sysdate)
and rc.revenue_code_id = nvl(ed.revenue_code_id,rc.revenue_code_id);

I also tried by removing the sysdate from the where conditions, but still same error message.
Rob van WijkJanuary 4, 2015 at 10:10 AM
My guess is that general restriction 7 (It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.) kicks in with your predicate "thrt.catalogue_change_id = (select ...)". You can check by removing that predicate and replacing all sysdates with a fixed date. If that still doesn't work, my advice is to post your question on OTN or Stack Overflow.

About Oracle

Pages

Sunday, May 31, 2009

Fast refreshable materialized view errors, part two: join MV's

UPDATE

10 comments: