物化视图包含错误结果

Question

我在Oracle 19c (版本19.15)中的物化视图的内容上有问题。我设法用这个脚本将这些问题提取为一个可重复的测试：

create table b(
    tsn varchar2(16) not null primary key,
    fid varchar2(256) not null
);

create table bs(
    tsn varchar2(16) not null constraint bet_stakes_fk references b,
    leg number(1) not null,
    amount number(10) not null,
    primary key (tsn, leg) using index compress 1
);

create materialized view log on b
    with primary key, rowid, sequence, commit scn (fid)
    including new values;

create materialized view log on bs
    with primary key, rowid, sequence, commit scn (amount)
    including new values;

create materialized view bsd_mv
refresh fast start with (sysdate - 1) next (sysdate + 1/14400)
as
select fid, leg, sum(amount), count(*)
from bs inner join b using (tsn)
group by fid, leg;

insert into b values ('a', 'o');
insert into bs values ('a', 1, 10);
commit;

delete from bs where tsn = 'a';
delete from b where tsn = 'a';
insert into b values ('a', 'o');
insert into bs values ('a', 1, 5);
commit;

等待10秒左右，然后选择

select * from bsd_mv;

随着脚本的不同运行，结果会有所不同，但通常的结果是

| Fid | Leg | Sum | Count |
| --- | --- | --- | ----- |
| o   |   1 |  15 |     3 |

..。在我期望的地方..。

| Fid | Leg | Sum | Count |
| --- | --- | --- | ----- |
| o   |   1 |   5 |     1 |

如果我运行视图所基于的查询，则总是得到预期的结果。

我是在设置中遗漏了什么，还是有错误的期望，还是在Oracle中触发了一个bug？

Answer 1

在Oracle的支持下，花了几个月的时间，但最终这被认为是一个错误.