Re: ORA-12052: cannot fast refresh materialized view

From: <fitzjarrell_at_cox.net>
Date: 14 Sep 2006 13:51:02 -0700
Message-ID: <1158267062.707548.309850@b28g2000cwb.googlegroups.com>

amy wrote:
> I have problem performing a fast refresh on a materialized view. I went
> through the documentation and it looks like I have met all the
> criterias for a fast refresh but I can't seems to get the MV created.
>
> Can someone point out what I have done incorrectly?
>
> create table emp (empid number, name varchar2(10), deptno number);
> alter table emp add constraint emp_pk primary key (empid);
> create table dept (deptno number, name varchar2(10));
> alter table dept add constraint dept_pk primary key (deptno);
> alter table emp add constraint emp_fk foreign key (deptno) references
> dept (deptno);
>
> create materialized view log on emp with primary key (name,deptno)
> including new values;
> create materialized view log on dept with primary key including new
> values;
>
> SQL> create materialized view emp_dept_mv
> 2 refresh fast as
> 3 select e.name from emp e, dept d where e.deptno=d.deptno;
> select e.name from emp e, dept d where e.deptno=d.deptno
> *
> ERROR at line 3:
> ORA-12052: cannot fast refresh materialized view GTTK.EMP_DEPT_MV
>
> thanks in advance.

For starters:

CAPABILITY_NAME                P RELATED_NUM      MSGNO MSGTXT
------------------------------ - ----------- ----------
----------------------------------------------------------------------------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N          45       2068 relation is not
a partitioned table
PCT_TABLE                      N          52       2068 relation is not
a partitioned table
REFRESH_FAST_AFTER_INSERT      N                   2172 the SELECT list
does not have the rowids of all the detail tables
REFRESH_FAST_AFTER_INSERT      N                   2079 mv log must
have ROWID
REFRESH_FAST_AFTER_INSERT      N                   2079 mv log must
have ROWID
REFRESH_FAST_AFTER_ONETAB_DML  N                   2146 see the reason
why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N                   2161 see the reason
why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N                   2157 PCT is not
possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N                   2158 general rewrite
is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE              N          45       2068 relation is not
a partitioned table
PCT_TABLE_REWRITE              N          52       2068 relation is not

a partitioned table

18 rows selected.

Making this change eliminates the missing rowid problems:

create materialized view log on emp with rowid, sequence(empid, name, deptno) including new values;
create materialized view log on dept with rowid, sequence(deptno, name) including new values;

I haven't time to play with this to work out the required query; the key, apparently, is having the select list return all rowids from the dept table (my educated guess). Possibly someone else can show where I've gone wrong and provide assistance.

David Fitzjarrell Received on Thu Sep 14 2006 - 15:51:02 CDT