Saturday, May 30, 2009

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

11 comments:

  1. Rob,

    it would be interesting to see the corresponding outputs of DBMS_MVIEW.EXPLAIN_MVIEW and/or DBMS_ADVISOR.TUNE_MVIEW for the restriction cases.

    Regards,
    Randolf

    ReplyDelete
  2. Hi Randolf,

    Thanks for the suggestion. It's a good opportunity to get to know dbms_advisor.tune_mview. And I just discovered that dbms_mview.explain_mview sometimes does give very accurate error messages. I will add the outputs somewhere this week.

    Regards,
    Rob.

    ReplyDelete
  3. Randolf,

    I changed my mind: I will not add the output to the current post, but create a new one with the mv_capabilities_table output in the near future.

    Regards,
    Rob.

    ReplyDelete
  4. Do you an idea on what restriction prevents me from creating a mv which has a exists clause, something like:

    create materialized view emp_mv
    refresh fast on commit
    as
    select e1.empno
    from myemp e1
    where exists ( select e2.empno
    from myemp e2
    where e2.empno = e1.empno
    )

    ReplyDelete
  5. @blogje:

    Even after adding a rowid column, this produces an ORA-12054, which basically means you are out of luck in terms of knowing what went wrong. My guess is that the restriction "It cannot contain nested queries that have ANY, ALL, or NOT EXISTS." should be expanded a little more to include EXISTS.

    Regards,
    Rob.

    ReplyDelete
  6. @Rob

    Do you use fast refresh mv's for ensuring data integrity?

    Like here: http://technology.amis.nl/blog/475/introducing-materialized-views-as-mechanism-for-business-rule-implementation-complex-declarative-constraints

    What do you think of stuff like this?

    ReplyDelete
  7. @Anonymous: At first, it sounds like a fantastic way to have a business rules validated on commit time. I recommended it myself here: http://rwijk.blogspot.com/2008/08/implementing-entity-rules.html.

    However, there are serious scalability issues involved with this type of solution, so now I am not that fond of this solution. I will blog about this in my current MV series. Probably it will be part 7 or 8.

    Regards,
    Rob.

    ReplyDelete
  8. Oh I didn't know that.

    I provided a solution based on mv's for enforcing business rules on stackoverflow.

    See: http://stackoverflow.com/questions/1209025/using-scripts-to-ensure-data-integrity-in-oracle/1217035#1217035

    I mention you articles too.

    (same anonymous aka tuinstoel on stackoverflow)

    ReplyDelete
  9. Hi Rob,

    Really good work. I came across restriction 3 when creating an mview that is not fast refresh.

    pg@10GR2>create materialized view emp_mv
    2 refresh complete on demand
    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


    pg@10GR2>pg@10GR2>select * from v$version where rownum = 1;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


    Weird, cannot find such restriction in the docs.

    Regards
    Peter
    (Sorry can't seem to make PRE tag work)

    ReplyDelete
  10. Hi,
    i liked very much your test cases for MVs!

    In the last couple of days i've been trying to figure a strange case of ORA-12054, with little success so far.

    In brief:
    i checked my create mv statement against DBMS_MVIEW.EXPLAIN_MVIEW and it is telling me that my materialized view is fast refreshable, however i can only define it as REFRESH FAST ON DEMAND, not ON COMMIT. I checked the various restrictions in the docs but i can't check on metalink as i work on XE so i haven't got access to the support knowledgebase.

    My master table is a normal heap table (so not IOT), it contains an XMLTYPE column and two URITYPE columns that i do not reference in the MV.
    The materialized view is a simple aggregate on one column with a COUNT(*), in the same schema, so no db links or extra object privileges are involved.
    I granted the user the ON COMMIT REFRESH privilege too.
    I tried virtually any combination of rowid, primary key and filter columns in the definition of the mview log.

    The nice part is that if clone the master table using a CREATE AS SELECT * FROM master, i can create the fast refreshable on commit materialized view that fails on the original one.

    This happens on both test and production system, so i am really wondering if both these instances have some bogus master table definition or if i am missing some of those restrictions mentioned in the metalink doc that i can't read.

    As far as i can see the only difference between the original master table and its clone are the constraints defined on another table having a foreign key referencing the primary key and a couple of triggers.

    Any ideas?

    Thanks
    Flavio

    ReplyDelete
  11. Hi Flavio,

    From your story I can't see anything obvious that you might have forgotten. Can you post a small test case for me to reproduce the issue?

    Regards,
    Rob.

    ReplyDelete