Complex materialized views and fast refresh

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.

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.
 

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

6 comments

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

   Reply

   1. Thanks – we’ll take a look at it and see what can be achieved in LiveSQL

      Reply

2. Hi Connor,
   I’d like to know if it is any way to create Materialized Views that contains analytic functions??
   Thanks.

   Reply

   1. Yes, but typically that will dramatically limit things like fast refresh

      eg ,take something as simple as

      select …., row_number() over ( order by creditlimit)
      from customers

      I must *always* scan the entire customers table before *any* kind of predicate can be applied because otherwise the row_number() results would become invalid (ie, not respect all of the customer data)

      Reply
3. Pingback: 2019-what grabbed your attention – Learning is not a spectator sport

4. Sounds like this got lifted in 19c and above.

   c##rajesh@PDB1> create or replace view vw
   2 as
   3 select e.rowid as emp_rowid, d.rowid as dept_rowid,
   4 e.empno , d.deptno
   5 from emp e,dept d
   6 where e.deptno = d.deptno;

   View created.

   c##rajesh@PDB1> create materialized view emp_mv
   2 build immediate
   3 refresh fast on demand
   4 enable query rewrite
   5 as
   6 select *
   7 from vw;

   Materialized view created.

   c##rajesh@PDB1> select banner_full from v$version;

   BANNER_FULL
   ——————————————————————————
   Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
   Version 19.3.0.0.0

   Reply

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