-
M. View Fast Refresh
All,
I am creating a Materialized view using joining of two tables. When trying for the FAST refresh, getting the message
Code:
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
Last edited by Thomasps; 08-05-2004 at 04:54 AM.
-
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.
-
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.
Code:
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
-
Why rowid and not primary key, if I may ask?
-
Originally posted by slimdave
Why rowid and not primary key, if I may ask?
Because
Code:
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
-
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)
-
Logically agreee. Still I am not getting way to build a M. View based a complex (join multiple tables) using primary key. Thanks Slim.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|