Basic Replication -- 4 : Data Dictionary Queries

Now that we have two Materialized Views against a Source table, how can we identify the relationship via the data dictionary ?

This is the query to the data dictionary in the database where the Source Table exists :

SQL> l
  1  select v.owner MV_Owner, v.name MV_Name, v.snapshot_site, v.refresh_method,
  2  l.log_table MV_Log_Name, l.master MV_Source,
  3  to_char(l.current_snapshots,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
  4  from dba_registered_snapshots v, dba_snapshot_logs l
  5  where v.snapshot_id = l.snapshot_id
  6* and l.log_owner = 'HEMANT'
SQL> /

MV_OWNER MV_NAME          SNAPSHOT_SITE      REFRESH_MET MV_LOG_NAME        MV_SOURCE             LAST_REFRESH_DATE
-------- ---------------- ------------------ ----------- ------------------ --------------------- ------------------
HEMANT   MV_OF_SOURCE     ORCLPDB1           PRIMARY KEY MLOG$_SOURCE_TABLE SOURCE_TABLE          16-SEP-19 22:41:04
HEMANT   MV_2             ORCLPDB1           PRIMARY KEY MLOG$_SOURCE_TABLE SOURCE_TABLE          16-SEP-19 22:44:37

SQL>

I have run the query on the DBA_REGISTERED_SNAPSHOTS and DBA_SNAPSHOT_LOGS because the join on SNAPSHOT_ID is not available between DBA_REGISTERED_MVIEWS and DBA_MVIEW_LOGS.  Similarly, the CURRENT_SNAPSHOTS column is also not available in DBA_MVIEW_LOGS.  These two columns are important when you have *multiple* MViews against the same Source Table.

Note the "Snapshot_Site" is required because the Materialized View can be in a different database.  In this example, the MViews are in the same database as the Source Table. 

The target database containing the MViews will not have the Source Table "registered" into a data dictionary view.  The Source Table will be apparently from the QUERY column of DBA_MVIEWS (also, if the Source Table is in a different database, look at the MASTER_LINK column to identify the Database Link that connects to the source database).


UPDATE :  In case you are wondering what query you'd write against the database containing the Materialized View(s), you can simply query DBA_MVIEWS.

SQL> l
  1  select mview_name, query, master_link, refresh_mode, refresh_method,
  2  last_refresh_type, to_char(last_refresh_date,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
  3  from dba_mviews
  4  where owner = 'HEMANT'
  5* order by 1 desc
SQL> /

MVIEW_NAME
------------
QUERY
--------------------------------------------------------------------------------
MASTER_LINK  REFRESH_M REFRESH_ LAST_REF LAST_REFRESH_DATE
------------ --------- -------- -------- ---------------------------
MV_OF_SOURCE
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" "DAT
E_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE"
             DEMAND    FAST     FAST     16-SEP-19 22:41:04

MV_2
select id, data_element_2
from source_table
             DEMAND    FORCE    FAST     16-SEP-19 22:44:37


SQL>

Here, the MASTER_LINK would specify the name of the Database Link used to connect to the Master (i.e. Source) table, if it was a different database.

REFRESH_MODE is ON DEMAND so that the MVs can be refreshed by either scheduled jobs or manually initiated calls -- as I've done in previous blog posts.  (The alternative can be ON COMMIT, if the Source Table and MV are in the same database).

LAST_REFRESH_TYPE is FAST, meaning that the refresh was able to use the MV Log on the Source Table to identify changes and merge them into the MV.  See the entries from the trace file that I've shown in the previous blog post.

Note the difference in the two REFRESH_METHOD values for the two MVs.
MV_OF_SOURCE was created as "refresh fast on demand" while "MV_2" was created as "refresh on demand".

We'll explore the implications of "REFRESH FAST" and just "REFRESH" alone in a subsequent blog post.

Question : Why does the QUERY look so different between MV_OF_SOURCE and MV_2 ?

Basic Replication -- 3 : Multiple Materialized Views

You can define multiple Materialized Views against the same Source Table with differences in :
a) the SELECT clause column list
b) Predicates in the WHERE clause
c) Joins to one or more other Source Table(s) in the FROM clause
d) Aggregates in the SELECT clause

Thus, for my Source Table, I can add another Materialized View :

SQL> create materialized view mv_2
  2  refresh on demand
  3  as select id, data_element_2
  4  from source_table;

Materialized view created.

SQL>
SQL> select count(*) from mlog$_source_table;

  COUNT(*)
----------
         0

SQL> insert into source_table
  2  values (5, 'Fifth','Five',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_source_table;

  COUNT(*)
----------
         1

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

PL/SQL procedure successfully completed.

SQL> select * from mv_of_source;

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
         5 Fifth           Five            16-SEP-19
       101 First           One             18-AUG-19
       103 Third           Three           18-AUG-19
       104 Fourth          Updated         09-SEP-19

SQL> select count(*) from mlog$_source_table;

  COUNT(*)
----------
         1

SQL>

Now that there are two MVs referencing the Source Table, the MV Log is not completely purged when only one of the two MVs is refreshed.  Oracle still maintains entries in the MV Log for the second MV to be able to execute a Refresh.

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
     XID$$
----------
         5 16-SEP-19 I N
FE
5.6299E+14


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

PL/SQL procedure successfully completed.

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from mv_2;

        ID DATA_ELEMENT_2
---------- ---------------
       101 One
       103 Three
       104 Updated
         5 Five

SQL>

The MV Log is "purged" only when the second (actually the last) MV executes a Refresh.  Of course, if more rows were inserted / updated in the Source Table between the Refresh of MV_OF_SOURCE and MV_2, there would be corresponding entries in the MV Log.

So, Oracle does use some mechanism to track MVs that execute Refresh's and does continue to "preserve" rows in the MV Log for MVs that haven't been refreshed yet.

As I've noted in two earlier posts, in 2007 and 2012, the MV Log (called "Snapshot Log" in the 2007 post) can keep growing for a long time if you have one or more Materialized Views that just aren't executing their Refresh  calls.

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?
.
.
.
.
.
.