Hello...

I am new to using materialized views (MV) in Oracle, and I am having problems making this one work. I have two tables, SWO_LINE and SWLN_ADD where I insert and update records.

I have created MV Logs for both tables as seen below:

CREATE MATERIALIZED VIEW LOG ON SWLN_ADD
WITH ROWID, PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON SWO_LINE
WITH ROWID, PRIMARY KEY
INCLUDING NEW VALUES;

I have also created a MV named C_SWOLNA_MV as seen below:

CREATE MATERIALIZED VIEW C_SWOLNA_MV 
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS 
(SELECT d.CCN,
        d.MAS_LOC,
        d.SWO_NUM,
        d.SWO_LINE,
        d.OPEN_DATE,
        a.OPEN_TIME,
        a.TECHNICIAN,
        d.FIN_CLOSE_DATE,
        d.ROWID    "SWO_LINE_ROWID",
        a.ROWID    "SWLN_ADD_ROWID"
   FROM SWO_LINE d, SWLN_ADD a
  WHERE     a.CCN = d.CCN
        AND a.MAS_LOC = d.MAS_LOC
        AND a.SWO_NUM = d.SWO_NUM
        AND a.SWO_LINE = d.SWO_LINE);

I have also created a trigger on the MV: the idea is that when a record is inserted into the MV, a record is also inserted in a table named C_SWOLNA, and if a specific date column is updated in the MV, then the record in the C_SWOLNA table is updated. The trigger code is below:

CREATE OR REPLACE TRIGGER C_SWOLNA_MV_AFT_INS
after insert or update on C_SWOLNA_MV
for each row
begin
   if inserting then
      begin
         insert into C_SWOLNA
         values (:new.CCN, :new.MAS_LOC, :new.SWO_NUM, :new.SWO_LINE, trunc(sysdate), to_char(sysdate, 'HH24MISS'), :new.TECHNICIAN);
         exception
            when dup_val_on_index then null;
      end;
   else
      if :old.FIN_CLOSE_DATE is null and :new.FIN_CLOSE_DATE is not null then
         begin
            update C_SWOLNA
            set OPEN_DATE = trunc(sysdate),
                OPEN_TIME = to_char(sysdate, 'HH24MISS')
            where CCN = :new.CCN
            and MAS_LOC = :new.MAS_LOC
            and SWO_NUM = :new.SWO_NUM
            and SWO_LINE = :new.SWO_LINE;
            exception
               when others then null;
         end;
      end if;
   end if;
end;

When I test this, I am getting a lot of inserts done to the C_SWOLNA table, but not a single update (I determined this by adding a logging mechanism in the trigger that writes to a temp table and shows me when inserting or updating as well as the keys). I have checked Oracle documentation regarding MVs, and I have even used the dbms_mview.explain_mview() procedure, and it looks like everything is working fine. See results of the procedure below.

REFRESH_COMPLETE Y  
REFRESH_FAST Y  
REFRESH_FAST_AFTER_INSERT Y  
REFRESH_FAST_AFTER_ONETAB_DML Y  
REFRESH_FAST_AFTER_ANY_DML Y  
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view 

What could be wrong with my setup here? Is it possible to have this MV work fine when inserting and also when updating a record in the MV?

Thanks in advance.

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

insert into t1
with rws as (
  select level x from dual
  connect by level <= 10
)
  select x, x from rws;
insert into t2 
with rws as (
  select level x from dual
  connect by level <= 10
)
  select x, x from rws;
commit;

create materialized view log on t1
  with rowid, primary key ( c2 )
  including new values;
create materialized view log on t2
  with rowid, primary key ( c2 )
  including new values;

create materialized view mv 
  refresh fast on commit
as 
  select t1.c1, t1.c2 t1c2, t2.c2 t2c2,
         t1.rowid t1rid, t2.rowid t2rid
  from   t1, t2 
  where  t1.c1 = t2.c1;
  
alter session set sql_trace = true;
update t2 set c2 = 0;
commit;
alter session set sql_trace = false;

DELETE FROM "CHRIS"."MV" SNA$
WHERE
 "T2RID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "CHRIS"."MLOG$_T2" "MAS$"
  WHERE "MAS$".XID$$ = :1  AND NOT        ("MAS$".DMLTYPE$$ = 'U' AND
  (sys.dbms_snapshot_utl.vector_compare(:B_CV0,
                  "MAS$".change_vector$$) = 0)))MAS$)

INSERT  /*+ NOAPPEND */ INTO "CHRIS"."MV" SELECT /*+ NO_MERGE("JV$") */
  "MAS$1"."C1","MAS$1"."C2","JV$"."C2","MAS$1".ROWID,"JV$"."RID$" FROM (
  SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "CHRIS"."T2" "MAS$" WHERE
  ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
  FROM "CHRIS"."MLOG$_T2" "MAS$"   WHERE "MAS$".XID$$ = :1  AND NOT
  ("MAS$".DMLTYPE$$ = 'U' AND
  (sys.dbms_snapshot_utl.vector_compare(:B_CV0,
                  "MAS$".change_vector$$) = 0)))) "JV$", "T1" AS OF
  SNAPSHOT(:B_SCN)  "MAS$1" WHERE "MAS$1"."C1"="JV$"."C1"