使用“不存在”的Oracle物化视图

我知道Oracle物化视图无法使用“not exists”子句快速刷新.
有工作吗？我尝试使用左外连接和()但这两个选项似乎也没有用.任何帮助表示赞赏

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);

解决方法

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

（编辑：淮北站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!