Oracle物化 View 与 "not exists"一起使用

Question

我知道 Oracle 物化 View 无法使用“不存在”子句快速刷新。有解决方法吗？我尝试使用左外连接和 (+)，但这两个选项似乎也不起作用。感谢任何帮助

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

启用快速刷新很棘手，有许多奇怪的限制和无用的错误消息。在这种情况下，您需要创建一个物化 View 日志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