Hello fellow Stackoverflowers,
TLDR: Are MVIEWs using UPDATE or DELETE + INSERT during refresh?
some time ago I ran into an obscure thing when I was fiddling whit materialized views in Oracle. Here is my example:
- 2 base tables
- MVIEW logs for both tables
- PKs for both tables
- MVIEW created as a join of these base tables
- PK for MVIEW
Here is an example code:
-- ========================= 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);
According to dbms_mview.explain_mview my MVIEW if capable of fast refresh.
Well in this particular case (not in example here) the MVIEW is referenced by an FK from some other table. Because of that, I found out, that when I do a change in one of these base tables and the refresh of MVIEW is triggered I got an error message:
ORA-12048: error encountered while refreshing materialized view "ABC"
ORA-02292: integrity constraint (ABC_FK) violated
I was like What the hell??. So I started digging - I created a trigger on that MVIEW. Something like this:
/* 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;
/
So I was able to see what is happening. According to my trigger, every time I do UPDATE in the base table (not INSERT nor DELETE) there is actually DELETE and INSERT operation on MVIEW table.
update tko_mview_test2_tb
set data = 'some sting'
where id = 1000;
commit;
Output
delete
insert
Is this correct way how refresh of MVIEW works? There is no updates on MVIEW table when refreshing MVIEW?
Regards, Tom
