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.
That's because there is no update to the MV!
When there's a join in the MV updates to the base table(s) become a delete+insert of the corresponding rows in the MV.
You can verify this by tracing the refresh. For example:
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;
In the trace file this creates, you'll see statements like:
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"
But no updates (to the MV).
So you'll have to find another way to achieve your goal. I would avoid triggers on MVs. As shown here, the refresh method used may be different to the one you expect.