Got it working. Thanks everyone for their pointers.
fitzjarrell_at_cox.net wrote:
> 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 - 19:27:33 CDT
