Sunday, May 31, 2009

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.

10 comments:

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

    ReplyDelete
  2. 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.

    ReplyDelete
  3. 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.

    ReplyDelete
  4. 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...

    ReplyDelete
  5. 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.

    ReplyDelete
  6. 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.

    ReplyDelete
  7. Fantastic post. Why cannot Oracle explain in this nice fashion.

    ReplyDelete
  8. 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.

    ReplyDelete
  9. 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.

    ReplyDelete