Oracle 没有子查询就不存在

我有4张表

table item_values
  (item_key, item_value)

table filter_model
  (filter_key)
  
table item_includes
  (include_key, filter_key_fk, item_key, include_value)

table item_excludes
  (exclude_key, filter_key_fk, item_key, exclude_value)

我想从每个filter_model的item_values中获取所有项目，这些项目与包含内容匹配，并且不在排除范围内

查询应该像

select item_values.item_key,
       item_values.item_value,
       filter_model.filter_key,
       item_includes.include_value
  from item_includes,
       filter_model,
       item_values
  where filter_model.filter_key=item_includes.filter_key_fk
    and item_values.item_value=item_includes.include_value
    and not exists (select 'x'
                      from item_excludes
                      where item_excludes.filter_key_fk=filter_model.filter_key
                        and item_excludes.include_value=item_values.item_value);

这工作正常，但我需要创建物化视图快速刷新的性能目的。
所以子查询禁止fast_refresh。

select item_values.item_key,
       item_values.item_value,
       filter_model.filter_key,
       item_excludes.exclude_value
  from item_excludes,
       filter_model,
       item_values;

我的想法是为排除 (获取所有排除的项目) 创建一个MV，以便快速刷新:

create materialized view mv_excluded
as
select item_values.item_key,
       item_values.item_value,
       filter_model.filter_key,
       item_excludes.exclude_value
  from item_excludes,
       filter_model,
       item_values
  where filter_model.filter_key=item_excludes.filter_key_fk
    and item_values.item_value=item_excludes.exclude_value;

然后创建包含的主MV，并删除mv_excluded中的行。
我该怎么做呢？

有一个技巧!

您可以将not exists查询写为外部联接，返回那些外部联接表中 (强制) 列为null的行。

你可以快速刷新这些:

create table t1 (
  c1 int primary key
);
create table t2 (
  c1 int primary key
);

insert into t1 values ( 1 );
insert into t1 values ( 2 );

insert into t2 values ( 1 );
commit;

create materialized view log on t1 with rowid including new values;
create materialized view log on t2 with rowid including new values;

create materialized view mv
refresh fast on commit
as 
  select t1.rowid t1rid, t2.rowid t2rid, t1.* 
  from   t1, t2
  where  t1.c1 = t2.c1 (+)
  and    t2.c1 is null;

select c1 from mv;  

C1   
   2 

insert into t2 values ( 2 );
commit;

select c1 from mv;  

no rows selected

insert into t1 values ( 3 );
commit;

select c1 from mv;  

C1   
   3