我知道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);
解决方法
启用快速刷新很棘手,有许多奇怪的限制和无用的错误消息.在这种情况下,您需要创建物理化视图日志WITH ROWID,使用()连接语法,并为每个表添加ROWID.
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
