物化视图快速刷新-在更新基表时插入和删除

Question

你好，各位Stackoverpowers，

TLDR:MVIEW在刷新期间是否使用UPDATE或DELETE + INSERT

不久前，当我在Oracle中摆弄物化视图时，我遇到了一件晦涩的事情。这是我的例子：

• 2个基表
• 两个表的MVIEW日志
• 两张桌子的PKs
• 作为这些基表的联接创建的MVIEW
• MVIEW PK

下面是一个示例代码：

-- ========================= DDL section =========================
/* drop tables */
drop table tko_mview_test_tb;
drop table tko_mview_test2_tb;
/* drop mview */
drop materialized view tko_mview_test_mv;
/* create tables */
create table tko_mview_test_tb as
select 1111 as id, 'test' as code, 'hello world' as data, sysdate as timestamp from dual
union
select 2222, 'test2' as code, 'foo bar', sysdate - 1 from dual; 

create table tko_mview_test2_tb as
select 1000 as id, 'test' as fk, 'some string' as data, sysdate as timestamp from dual;
/* create table PKs */  
alter table tko_mview_test_tb
add constraint mview_test_pk
primary key (id);
alter table tko_mview_test2_tb
add constraint mview_test2_pk
primary key (id);
/* create mview logs */
create materialized view log
on tko_mview_test_tb
with rowid, (data);

create materialized view log
on tko_mview_test2_tb
with rowid, (data);

/* create mview */
create materialized view tko_mview_test_mv
refresh fast on commit
as select a.code
, a.data
, b.data as data_b
, a.rowid as rowid_a
, b.rowid as rowid_b 
from tko_mview_test_tb a
join tko_mview_test2_tb b on b.fk = a.code;
/* create mview PK */ 
alter table tko_mview_test_mv
add constraint mview_test3_pk
primary key (code);    

根据dbms_mview.explain_mview，我的MVIEW是否能够快速刷新

在这种特殊情况下(此处不是示例(，MVIEW由其他表中的FK引用。正因为如此，我发现，当我对其中一个基表进行更改并触发MVIEW刷新时，我收到了一条错误消息：

ORA-12048: error encountered while refreshing materialized view "ABC"
ORA-02292: integrity constraint (ABC_FK) violated

我当时想怎么了。所以我开始挖掘——我在MVIEW上创建了一个触发器。类似这样的东西：

/* create trigger on MVIEW */  
create or replace trigger tko_test_mview_trg
after insert or update or delete
on tko_mview_test_mv
referencing old as o new as n
for each row
declare
begin
if updating then
dbms_output.put_line('update');
elsif inserting then
dbms_output.put_line('insert');
elsif deleting then
dbms_output.put_line('delete');
end if;  
end tko_test_mview_trg;
/

所以我能够看到发生了什么。根据我的触发器，每次我在基表中执行UPDATE(不是INSERT或DELETE(时，实际上都会对MVIEW表执行DELETE和INSERT操作。

update tko_mview_test2_tb
set data = 'some sting'
where id = 1000; 
commit;

输出

delete
insert

这是刷新MVIEW的正确方式吗？刷新MVIEW时，MVIEW表上没有更新？

谨致问候，Tom

Answer 1

从oracle 12.1升级到oracle 19.x 后，我们也看到了同样的行为

新创建的mviews的行为似乎相同，在刷新期间删除/插入，而不是"预期"的更新。不确定是坏是错。。。。。但它可以"固定"。

应用补丁30781970(不要忘记_fix_control(并重新创建mview。。。。。

参考：Bug 30781970-MVIEW REFRESH失败，ORA-1错误，MVIEW上存在触发器(Doc ID 30781970.8(