web analytics
Materialized Views in Oracle Options
codeling
Posted: Friday, August 3, 2018 10:43:54 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 978
Points: 4113

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

cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:

SQL> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select a.rowid dept_rowid
  5       , b.rowid emp_rowid
  6       , a.deptno
  7    from dept a
  8       , emp b
  9   where a.deptno = b.deptno
 10  /

Materialized view created.
Sponsor
Posted: Friday, August 3, 2018 10:43:54 PM
codeling
Posted: Friday, August 3, 2018 10:47:50 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 978
Points: 4113

the materialized view logs for aggregate MV's need all columns and the "including new values" clause:


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.

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2018 Digcode.com. All rights reserved.