Complex materialized views and fast refresh

Just a quick discovery that came across the AskTOM “desk” recently. We have an outstanding bug in some instances of fast refresh materialized views when the definition of the materialized view references a standard view.

Here’s a simple demo of the issue – I’ll use a simplified version of the EMP and DEPT tables, linked by a foreign key in the usual way:


SQL> create table dept(dept_id number(10) primary key, dname varchar2(20));
 
Table created.
 
SQL> create table emp(empid number(20) primary key, ename varchar2(20), sal number(10,2), dept_id number(10) references dept(dept_id));
 
Table created.
 
SQL> insert into dept values(10,'IT');
 
1 row created.
 
SQL> insert into dept values(20,'HR');
 
1 row created.
 
SQL> insert into dept values(30,'MAT');
 
1 row created.
 
SQL> insert into emp values(1,'MIKE',20000,10);
 
1 row created.
 
SQL> insert into emp values(2,'JOHN',30000,20);
 
1 row created.
 
SQL> insert into emp values(3,'SUE',20000,20);
 
1 row created.
 
SQL> insert into emp values(4,'TOM',40000,30);
 
1 row created.
 
SQL>
SQL> commit;
 
Commit complete.

One of the cool things with materialized views is that even with complicated SQL definitions (such as joins), the materialized view can still be fast refreshable as long as the materialized view logs and database constraints are correctly defined.


SQL> create materialized view log on dept
  2  with rowid, primary key, sequence
  3  including new values;
 
Materialized view log created.
 
SQL>
SQL> create materialized view log on emp
  2  with rowid, primary key, sequence
  3  including new values;
 
Materialized view log created.
 
SQL> create materialized view mv
  2  --build deferred
  3  refresh fast on demand
  4  with primary key
  5  enable query rewrite
  6  as
  7  select a.rowid erowid,b.rowid drowid ,b.dname, a.*
  8  from emp a, dept b
  9  where a.dept_id=b.dept_id;
 
Materialized view created.
 

Note: See the documentation for DBMS_MVIEW.EXPLAIN_MVIEW for how to check on the refresh characteristics of a materialized view (or potential materialized view).

Now I’ll repeat the same experiment, but I’ll wrap that SQL that joins EMP and DEPT within a standard view called VW. Since a view is just stored SQL text, and the previous usage of the same SQL worked fine, we’d expect no difference in functionality.  However, the results do not meet the expectation.


SQL> drop materialized view mv ;
 
Materialized view dropped.
 
SQL>
SQL> create or replace view vw
  2  as
  3  select a.rowid erowid,b.rowid drowid ,b.dname, a.*
  4  from emp a, dept b
  5  where a.dept_id=b.dept_id;
 
View created.
 
SQL>
SQL> create materialized view mv
  2  --build deferred
  3  refresh fast on demand
  4  with primary key
  5  enable query rewrite
  6  as
  7  select * from vw;
select * from vw
              *
ERROR at line 7:
ORA-12015: cannot create a fast refresh materialized view from a complex query

This is not a flaw in our DDL – it is a bug in the database that will be fixed in due course. So if you have standard views being used within your materialized view definitions, and you are getting unexpected restrictions on whether the materialized views can be fast refreshed, try a simple workaround of in-lining the view text directly.  You might have hit this bug.

2 thoughts on “Complex materialized views and fast refresh

  1. Hello Connor,

    I tried to use LiveSQL just to see whether DBMS_MVIEW.EXPLAIN_VIEW can supply more details,
    and I encountered another bug …

    I created the MV_CAPABILITIES_TABLE as required and created an MV2 mview as
    follows:

    create materialized view mv2
    refresh force on demand
    with primary key
    enable query rewrite
    as
    select * from vw’ ;

    When trying to explain it, I got the following strange error:

    begin
    DBMS_MVIEW.EXPLAIN_MVIEW (‘MV2’);
    end;
    /

    ORA-32341: The EXPLAIN_MVIEW facility failed to explain the materialized view “SQL_DMRSXDVGBZRCKZFIRKXACVLHO”.”MV2″
    ORA-06512: at “SYS.DBMS_XRWMV”, line 22
    ORA-06512: at “SYS.DBMS_XRWMV”, line 42
    ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3881
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 224
    ORA-06512: at line 2
    ORA-06512: at “SYS.DBMS_SQL”, line 1721

    The alternative VARRAY variant also does not work, as I get this privilege error:

    ORA-06550: line 2, column 17:
    PLS-00201: identifier ‘SYS.EXPLAINMVARRAYTYPE’ must be declared

    Finally, I tried the “direct SQL”, and then I found the culprit:

    declare
    l_sql varchar2(2000) :=
    ‘create materialized view mv2
    refresh force on demand
    with primary key
    enable query rewrite
    as
    select * from vw’ ;
    begin

    DBMS_MVIEW.EXPLAIN_MVIEW (l_sql);
    end;
    /

    ORA-30377: table APEX_PUBLIC_USER.MV_CAPABILITIES_TABLE not found
    ORA-06512: at “SYS.DBMS_XRWMV”, line 22
    ORA-06512: at “SYS.DBMS_XRWMV”, line 42
    ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3881
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 224
    ORA-06512: at line 11
    ORA-06512: at “SYS.DBMS_SQL”, line 1721

    I understand that, unfortunately, this is a LiveSQL environment problem …

    The DBMS_MVIEW procedure is looking for the MV_CAPABILITIES_TABLE under
    the SESSION user’s schema, instead of the CURRENT user schema.

    The Oracle packages documentation explicitly specifies that the table should be in the current
    schema !

    It looks to be that this belongs to the same “problem family” as that of using DBMS_XPLAN …
    which we already discussed.

    I think that this problem should be corrected, but it looks to me that it would be best if you or
    another developer advocate will look after it …

    In the meantime, anyway, maybe the VARRAY variant could be made to work.

    Cheers & Best Regards,
    Iudith Mentzel

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.