CODEWITHSUNDEEP
oracle-databaseplsqloracle19c
kovalensue
kovalensue

Reputation: 134

Materialized view fast refresh - insert and delete when updating base table

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:

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

Upvotes: 4

Views: 2648

Answers (1)

Dennis
Dennis

Reputation: 51

We have seen the same behavior after upgrading from oracle 12.1 to oracle 19.x

Newly created mviews seems to behave the same, a delete/insert during the refresh instead of the 'expected' update. Not sure if it is bad or wrong.....but it can be 'fixed'.

Apply patch 30781970 (don't forget _fix_control) and recreate the mview.....

Reference: Bug 30781970 - MVIEW REFRESH IS FAILING WITH ORA-1 ERROR WITH TRIGGER PRESENT ON MVIEW (Doc ID 30781970.8)

Upvotes: 5

Related Questions