Basic Replication -- 2b : Elements for creating a Materialized View

Continuing the previous post, what happens when there is an UPDATE to the source table ?

SQL> select * from source_table;

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
         1 First           One             18-AUG-19
         3 Third           Three           18-AUG-19
         4 Fourth          Four            18-AUG-19

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from rupd$_source_table;

no rows selected

SQL>
SQL> update source_table
  2  set data_element_2 = 'Updated', date_col=sysdate
  3  where id=4;

1 row updated.

SQL> select * from rupd$_source_table;

no rows selected

SQL> commit;

Commit complete.

SQL> select * from rupd$_source_table;

no rows selected

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
     XID$$
----------
         4 01-JAN-00 U U
18
8.4443E+14


SQL>

So, it is clear that UPDATES, too, go to the MLOG$ table.

What about multi-row operations ?

SQL> update source_table set id=id+100;

3 rows updated.

SQL> select * from rupd$_source_table;

no rows selected

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
     XID$$
----------
         4 01-JAN-00 U U
18
8.4443E+14

         1 01-JAN-00 D O
00
1.4075E+15

       101 01-JAN-00 I N
FF
1.4075E+15

         3 01-JAN-00 D O
00
1.4075E+15

       103 01-JAN-00 I N
FF
1.4075E+15

         4 01-JAN-00 D O
00
1.4075E+15

       104 01-JAN-00 I N
FF
1.4075E+15


7 rows selected.

SQL>

Wow ! Three rows updated in the Source Table translated to 6 rows in the MLOG$ table ! Each update row was represented by an DMLTYPE$$='D' and OLD_NEW$$='O'  followed by a DMLTYPE$$='I' and OLD_NEW$$='N'.   So that should mean "delete the old row from the materialized view and insert the new row into the materialized view" ??

(For the time being, we'll ignore SNAPTIME$$ being '01-JAN-00').

So an UPDATE to the Source Table of a Materialized View can be expensive during the UPDATE (as it creates two entries in the MLOG$ table) and for subsequent refresh's as well !

What happens when I refresh the Materialized View ?

SQL> execute dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> execute dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> execute dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL>

The session trace file shows these operations (I've excluded a large number of recursive SQLs and SQLs that were sampling the data for optimisation of execution plans):

update "HEMANT"."MLOG$_SOURCE_TABLE" 
set snaptime$$ = :1  
where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

/* QSMQ VALIDATION */ ALTER SUMMARY "HEMANT"."MV_OF_SOURCE" COMPILE

select 1 from "HEMANT"."MLOG$_SOURCE_TABLE" 
where snaptime$$ > :1 
and ((dmltype$$ IN ('I', 'D')) or  (dmltype$$ = 'U' and old_new$$ in ('U', 'O') 
and   sys.dbms_snapshot_utl.vector_compare(:2, change_vector$$) = 1)) 
and rownum = 1

SELECT /*+ NO_MERGE(DL$) ROWID(MAS$) ORDERED USE_NL(MAS$) NO_INDEX(MAS$) PQ_DISTRIBUTE(MAS$,RANDOM,NONE) */ 
COUNT(*) cnt  
FROM ALL_SUMDELTA DL$, "HEMANT"."SOURCE_TABLE"  MAS$  
WHERE DL$.TABLEOBJ# = :1 AND DL$.TIMESTAMP > :2 AND DL$.TIMESTAMP <= :3 
AND  MAS$.ROWID BETWEEN DL$.LOWROWID AND DL$.HIGHROWID

select dmltype$$, count(*) cnt from "HEMANT"."MLOG$_SOURCE_TABLE"  
where snaptime$$ > :1 and snaptime$$ <= :2  
group by dmltype$$  order by dmltype$$

delete from "HEMANT"."MLOG$_SOURCE_TABLE" 
where snaptime$$ <= :1

and this being the refresh (merge update) of the target MV

DELETE FROM "HEMANT"."MV_OF_SOURCE" SNAP$ 
WHERE "ID" IN 
(SELECT * FROM (SELECT MLOG$."ID" 
  FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ 
  WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'I')) 
  AS OF SNAPSHOT(:B_SCN) )

/* MV_REFRESH (MRG) */ MERGE INTO "HEMANT"."MV_OF_SOURCE" "SNA$" USING 
(SELECT * FROM (SELECT CURRENT$."ID",CURRENT$."DATA_ELEMENT_1",CURRENT$."DATA_ELEMENT_2",CURRENT$."DATE_COL" 
FROM (SELECT "SOURCE_TABLE"."ID" "ID","SOURCE_TABLE"."DATA_ELEMENT_1" "DATA_ELEMENT_1","SOURCE_TABLE"."DATA_ELEMENT_2" "DATA_ELEMENT_2","SOURCE_TABLE"."DATE_COL" "DATE_COL" 
FROM "SOURCE_TABLE" "SOURCE_TABLE") CURRENT$, 
(SELECT DISTINCT MLOG$."ID" FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "SNAPTIME$$" > :1 
AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."ID" = LOG$."ID") AS OF SNAPSHOT(:B_SCN) )"AV$" ON ("SNA$"."ID" = "AV$"."ID") 
WHEN MATCHED THEN UPDATE  SET "SNA$"."DATA_ELEMENT_1" = "AV$"."DATA_ELEMENT_1","SNA$"."DATA_ELEMENT_2" = "AV$"."DATA_ELEMENT_2","SNA$"."DATE_COL" = "AV$"."DATE_COL" 
WHEN NOT MATCHED THEN INSERT  (SNA$."ID",SNA$."DATA_ELEMENT_1",SNA$."DATA_ELEMENT_2",SNA$."DATE_COL") 
VALUES (AV$."ID",AV$."DATA_ELEMENT_1",AV$."DATA_ELEMENT_2",AV$."DATE_COL")

So, we see a large number of intensive operations against the MLOG$ Materialized View Log object.

And on the MV, there is a DELETE followed by a MERGE (UPDATE/IINSERT)


Two takeaways :
1.  Updating the Source Table of a Materialized View can have noticeable overheads
2.  Refreshing a Materialized View takes some effort on the part of the database

(Did you notice the strange year 2100 date in the update of the MLOG$ table?
.
.
.
.
.
.