M. View Fast Refresh

[Thomasps]

All,

I am creating a Materialized view using joining of two tables. When trying for the FAST refresh, getting the message

SQL> exec dbms_mview.refresh('crew_log','F');
BEGIN dbms_mview.refresh('crew_log','F'); END;

*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
"REPLDBA"."CREW_LOG"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

Originally my understanding was, FAST refresh is not possible on M. Views based complex queries ( M. Views with joins and functions). But please see the following link

http://www.oracle.com/technology//pr...ily/jul05.html

I have Oracle 9.2.0 in both ends and the OS is IBM AIX. M. View logs as well created. If the above link is true, why I am getting the problem?

Many Thanks

[slimdave]

Use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to get a list of the capabilities of the MV -- it may point to a problem with the structure, or more likely to a bug in Oracle. Some of the fast refresh code seems to be a little ropy.

[Thomasps]

Thanks Slimdave. I was making two mistakes

1. M. View log created without the option "WITH ROWID"
2. ROWIDs were not included in the select list.

This is my R&D result which is going to implement in UAT soon.

create table emp (empno number, deptno number, name varchar2(20)) ;

create table dept (deptno number, deptname varchar2(20));

alter table emp add constraint empno_pk primary key  (empno);
alter table dept add constraint deptno_pk primary key  (deptno);

create MATERIALIZED VIEW LOG ON emp
  WITH ROWID;
create MATERIALIZED VIEW LOG ON dept
  WITH ROWID;

CREATE MATERIALIZED VIEW emp_dep
TABLESPACE users
STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 )
REFRESH fast   
AS
select e.empno,e.deptno,e.name,d.deptname,d.rowid drid,e.rowid erid,d.deptno ddeptno
from emp e ,dept d
where e.deptno = d.deptno;

Thomas

[slimdave]

Why rowid and not primary key, if I may ask?

[Thomasps]

Originally posted by slimdave
Why rowid and not primary key, if I may ask?

Because

SQL> create MATERIALIZED VIEW LOG ON emp   WITH primary key;

Materialized view log created.

SQL> create MATERIALIZED VIEW LOG ON dept   WITH primary key;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW emp_dep
TABLESPACE    users
STORAGE (     INITIAL 1M NEXT 1M PCTINCREASE 0 )
REFRESH fast   
AS
select  e.empno,e.deptno,e.name,d.deptname,d.deptno ddeptno
from emp    e ,dept d
where e.deptno = d.deptno;
from emp e ,dept d
     *
ERROR at line 7:
ORA-12052: cannot fast refresh materialized view THOMAS.EMP_DEP


SQL> CREATE MATERIALIZED VIEW emp_dep
TABLESPACE users
STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 )
REFRESH fast   
AS
select e.empno,e.deptno,e.name,d.deptname,d.rowid drid,e.rowid erid,d.deptno ddeptno
  from emp e ,dept d
where e.deptno = d.deptno;
from emp e ,dept d
     *
ERROR at line 7:
ORA-12032: cannot use rowid column from materialized view log on
"THOMAS"."DEPT"

If I can use Primary key for JOIN based M. Views, can you please correct me where I am wrong? Many Thanks

Thomas

[slimdave]

The use of primary key/rowid ought not to affect whether you can fast refresh or not, since it just controls whether the materialized view log stores changes against the rowid of the base table or the primary key. The use of primary key-based MV logs is more robust -- rowid-based logs get cleared if you move the table, and are really only there for backwards compatibility (although they're handy for DW fact tables that have no PK)

[Thomasps]

Logically agreee. Still I am not getting way to build a M. View based a complex (join multiple tables) using primary key. Thanks Slim.