Oracle materialized view using with "not exists"

Question

I know Oracle materialized views cannot be fast refreshed with "not exists" clause. Is there a work around. I tried using left outer join and (+) but these 2 options too didnt seem to work. Any help is appreciated

create materialized view mv_myview refresh fast as 
select a.* 
from tableA a 
where 
    not exists (select * from tableB b where a.my_id = b.my_id); 

Answer 1

Enabling fast refresh is tricky, there are many strange restrictions and unhelpful error messages. In this case, you need to create a materialized view log WITH ROWID, use the (+) join syntax, and add a ROWID for each table.

create table tablea(my_id number primary key, a number);
create table tableb(my_id number primary key, b number);

create materialized view log on tablea with rowid;
create materialized view log on tableb with rowid;

create materialized view mv_myview refresh fast on commit as 
select a.my_id, a.a, b.b, a.rowid a_rowid, b.rowid b_rowid
from tableA a, tableB b
where a.my_id = b.my_id(+)
    and b.My_id IS NULL;

insert into tablea values(1, 1);
commit;

select * from mv_myview;

MY_ID  A  B  A_ROWID             B_ROWID
-----  -  -  -------             -------
1      1     AAAUH3AAEAAC+t0AAA  

Answer 2

Executing your query under oracle 11, I've got the following error:

Using a LEFT JOIN, I had the same problem:

create materialized view mv_myview refresh fast as 
select a.* 
from tableA a LEFT JOIN tableB b ON a.my_id = b.my_id
where 
    b.id IS NULL; 

Same problem using NOT IN...

create materialized view mv_myview refresh fast as 
select a.* 
from tableA a 
where 
    a.my_id not in (select b.my_id from tableB b); 

First aid informations are quite clear:

ORA-12015: cannot create a fast refresh materialized view from a complex query Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries. Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.

The problem seems impossible. You'll have to change the view type.