Materialized Views Concepts

Materialized view concept: Why do we need materialized view?

Materialized views are nothing but views created on the base table and having data which is extracted from the base table.
How is materialized view different from the normal view.

Difference # 1:

Normal view does not contain data. It is just a transparent layer on the top of base

Materialized view contains data and additional space is required to create materialized view.
Difference # 2:

To use normal view, a user needs to provide the view name in the query

To use materialized view user does not need to provide materialized view name (although a user can, but its not required.)

Materialized views are required mainly for 2 reasons

1) Advanced replication of data from one location (database) to another location (database)

2) Summarizing the data in the table

When we are replicating the table from remote location to local location our queries can access the same data from local location which can lead to improved query performance.
Replication of data is possible using materialized views.

You can use materialized views to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggregations such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time.

Based on the above definitions we have 3 situations where materialized views can be used:

1) Materialized Views for Data Warehouses

In data warehouses, you can use materialized views to precompute and store aggregated data such as the sum of sales. Materialized views in these environments are often referred to as summaries, because they store summarized data. They can also be used to precompute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.

2) Materialized Views for Distributed Computing

In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at those sites with conflict resolution methods. These replica materialized views provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.

3) Materialized Views for Mobile Computing

You can also use materialized views to download a subset of data from central servers to mobile clients, with periodic refreshes and updates between clients and the central servers.

Its difficult to understand everything before we create a materialized view. So lets create a MView and understand every clause that can be given in creating MVIew.

Creating Materialized View:

Here goes the syntax of creating materialized view

CREATE MATERIALIZED VIEW SCHEMA.NAME

(COL1, COL2, COL3 … )

ON PREBUILT TABLE / BUILD IMMEDIATE / BUILD DEFERRED

WITH / WITHOUT REDUCED PRECISION

USING INDEX / USING NO INDEX

TABLESPACE

FOR UPDATE

REFRESH FAST / COMPLETE /  FORCE / ON DEMAND / ON COMMIT / START WITH .. NEXT /

REFRESH WITH PRIMARY KEY / ROWID

ENABLE / DISABLE QUERY REWRITE

This syntax is no way complete and there are many more clause related to storage, constraints, physical properties etc. But this is a basic DDL statement that is used most of the time to create materialized view.
If you want to see complete details of creating MView with all clauses, check reference section to get documentation link.

Lets discuss about each clause and what these values means to us.

ON PREBUILT TABLE

When you create a materialized view you can create the same on pre-built table. For example, before creating MView you were managing data replication using software and you were keeping a local table updated with latest data from some other master site. Now when you are implementing MView its a good option to use same table as prebuilt table and create MView on top of that. This way you can stop software replication and start using Mview.

If you don’t have any prebuilt table you can skip that clause along with “WITH REDUCED PRECISION” clause. If you are not using “ON PREBUILT TABLE” clause than you can use with “BUILD IMMEDIATE” or “BUILD DEFERRED” clause.
BUILD IMMEDIATE will immediately fetch the data info Mview. BUILD DEFERRED clause will defer fetching of data unless you do complete refresh manually.

Following things to be considered in case of PREBUILT Table

• Materialized view name should be same as prebuilt table name. Master table can have different name
• PREBUILT table can have extra column or less columns compared to master table, but Mview definition should include only those columns which are present in both master table and prebuilt table and corresponding columns must have matching datatypes.
• If you don’t have any prebuilt table, create materialized view statement will create a table but it won’t be a prebuilt table. Meaning that if you drop Mview, your table will also be dropped (unless you use “preserve table” clause). But if its a prebuilt table, table will not be dropped even if you drop MView. This way you can retain data. So its essential to have a table marked as prebuilt table.

If you created materialized view without prebuilt table, it will create a table and there is a way to change that table as prebuilt table. So that if you drop materialized view some time in future, table will stay. Following update statement will convert a table into prebuilt table

update sys.snap$ set flag = 2228321 where vname = '';

commit;

Above statement will convert a table into prebuilt table.

After that if you run the statement “drop materialized view ;” it won’t drop prebuilt table.

Alternatively if you don’t have prebuilt table and you want to drop snapshot preserving the table you can use following statement

drop materialized view preserve table;

WITH / WITHOUT REDUCED PRECISION

Specify WITH REDUCED PRECISION to authorize the loss of precision that will result if the precision of columns in materialized view does not match with precision of column result returned by subquery. Remember that subquery is build on table on master site.
So this clause makes sense if you are using different precision of columns on Mview site than on master site.
WITHOUT REDUCED PRECISION will require the precision of columns in Mview to be exactly same as that of columns in table on master site (precision retuned by subquery).

USING INDEX

Using index clause can be used to specify the storage clause for index that will be created on materialized view. You can also specify the tablespace that should be used for all indexes on materialized view.
If you are creating ROWID based materialized view then “USING INDEX” clause will create default index like I_SNAP$_ which will be useful in internal MView management.
Using “NO INDEX VLAUSE” will prevent creating this default index and also you cannot specify storage clause and tablespace clause.

TABLESPACE

This specifies the tablespace in which to create MVIEW.

ENABLE / DISABLE QUERY REWRITE

This clause will specify whether to enable or disable query rewrite on this MView.
One of the advantages of having materialized view is that our queries will automatically start using it (just like index)without doing any changes to the SQL. This capability is provided by query rewrite functionality.
The optimizer uses two different methods to recognize when to rewrite a query in terms of a materialized view. The first method is based on matching the SQL text of the query with the SQL text of the materialized view definition. If the first method fails, the optimizer uses the more general method in which it compares joins, selections, data columns, grouping columns, and aggregate functions between the query and materialized views.

You can find more details about query rewrite feature in link http://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm

REFRESH FAST / COMPLETE /  FORCE / ON DEMAND / ON COMMIT / START WITH .. NEXT /

Fast refresh can be based on primary key or it could be based on ROWID.
If fast refresh if based on primary key then, master table should have primary key defined. MView log will have primary key included. For fast refresh to work, you must have MLOG created on master table on master site using following commands

create materialized view log on ;

Fast refresh is most efficient way of maintaining data as it brings only the changed data from master site to local MView. It maintains the changes in MLOG.
Not all Mviews can be fast refreshed. You can check the restrictions on creating fast refreshable materialized view at http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007028

Complete refresh does not need any MLOG. When we do complete refresh, complete data of master table is brought into MView. This method can be used for small tables.

Refresh Force will first try to refresh the Mview using fast method and if it doesn’t work, it will go for complete refresh.

Refresh On Demand will need DBAs to schedule a job which will do the refresh of Mview on periodic basis. If you don’t want to setup a job, you have to decide the schedule and refresh Mview yourself.

Refresh On commit will refresh Mview if a transaction gets committed on master site. We have to be careful with this option, if master site is located far away and commit rate is high, this is not a good option. Everytime a commit happens, it will try to refresh Mview, which is going to take time and eventually your transactions on master site will slow down.

Refresh Start with .. Next is used when we want to define the schedule in Mview definition while creating MView itself. This way Mview gets refreshed as per defined schedule

I tried to give basic overview of Materialized view and creating materialized view. Below are some examples of creating materialized view using above clause

Examples of Mview Creation

Example 1:

create materialized view T_REP

 ( OWNER,

   SUBOBJECT_NAME ,

   OBJECT_ID,

   DATA_OBJECT_ID ,

   OBJECT_TYPE,

   CREATED,

   LAST_DDL_TIME,

   TIMESTAMP,

   STATUS ,

   TEMPORARY,

   GENERATED,

   SECONDARY,

   NAMESPACE,

   EDITION_NAME)

 BUILD IMMEDIATE

 REFRESH ON DEMAND

 as

 select OWNER,

        SUBOBJECT_NAME ,

        OBJECT_ID,

        DATA_OBJECT_ID ,

        OBJECT_TYPE,

        CREATED,

        LAST_DDL_TIME,

        TIMESTAMP,

        STATUS ,

        TEMPORARY,

        GENERATED,

        SECONDARY,

        NAMESPACE,

        EDITION_NAME

 from t@DB1;

Example 2:

Assuming we have primary key on master table and materialized view log on master site

create materialized view T_REP

 ( OWNER,   SUBOBJECT_NAME ,   OBJECT_ID,   DATA_OBJECT_ID ,   OBJECT_TYPE,

   CREATED,   LAST_DDL_TIME,   TIMESTAMP,   STATUS ,   TEMPORARY,   GENERATED,

   SECONDARY,   NAMESPACE,   EDITION_NAME) ON PREBUILT TABLE

 WITH REDUCED PRECISION

 USING INDEX TABLESPACE OPS_IDX

 REFRESH FAST ON DEMAND

 ENABLE QUERY REWRITE

 as

 select OWNER,        SUBOBJECT_NAME ,        OBJECT_ID,        DATA_OBJECT_ID ,

        OBJECT_TYPE,        CREATED,        LAST_DDL_TIME,        TIMESTAMP,

        STATUS ,        TEMPORARY,        GENERATED,        SECONDARY,        NAMESPACE,

        EDITION_NAME

 from t@DB1;

DBA_SNAPSHOTS (On MView site) Most important table for checking MView info is DBA_SNAPSHOTS.
We need to query this table on snapshot site (where MView is created)

SQL>select name, table_name, MASTER, MASTER_LINK, REFRESH_METHOD, UPDATABLE , LAST_REFRESH, STATUS, PREBUILT, 

REFRESH_MODE from dba_snapshots where name = 'T_REP';

NAME TABLE_NAME MASTER MASTER_LINK REFRESH_MET UPD LAST_REFRESH STATUS PRE REFRESH_MODE

---------- ---------- -------------------- ------------------------------ ----------- --- 

T_REP T_REP T @"DB1.Vamsi" PRIMARY KEY NO 2012-10-13:22:26 UNKNOWN YES DEMAND

DBA_REFRESH_CHILDREN (On Mview Site)

we can group multiple materialized views into a group and refresh all materialized view all at once.
This can be done by creating a refresh group.
You can see all steps to create refresh group at http://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmviewgroup.htm
Once you create refresh group and add MView to it, you can see the info in MVIEW_REFRESH_GROUPS table

BEGIN

   DBMS_REFRESH.MAKE (

      name => 'TEST_REF_GROP',

      list => '', 

      next_date => SYSDATE, 

      interval => 'SYSDATE + 1/24');

END;

/

BEGIN

   DBMS_REFRESH.ADD (

      name => 'TEST_REF_GROP',

      list => 'T_REP',

lax => FALSE );

END;

/

SQL>select name, RNAME, REFGROUP, INTERVAL, TYPE from dba_refresh_children where name = 'T_REP';

NAME       RNAME REFGROUP INTERVAL TYPE

------------------------------ ------------------------------ ---------- 

T_REP       TEST_REF_GROP     3693 SYSDATE + 1/24 SNAPSHOT

SQL>

DBA_SNAPSHOT_LOGS (On Master site):

If we want to check information about MLOG table, we can view this table.

SQL>select master, LOG_TABLE, ROWIDS, PRIMARY_KEY, SNAPSHOT_ID, CURRENT_SNAPSHOTS 

from dba_snapshot_logs where master = 'T';

MASTER       LOG_TABLE       ROW PRI SNAPSHOT_ID CURRENT_SNAPSHOT

------------------------------ ------------------------------ --- 

T       MLOG$_T       NO  YES     15119 2012-10-13:22:26

Since this is primary key based MLOG, we can see YES for primary key column. CURRENT_SNAPSHOT gives when was this last refreshed. This is same as LAST_REFRESH column in DBA_SNAPSHOTS

DBA_REGISTERED_SNAPSHOTS (On Master site):

To Check how many sites are registered for 1 master table, we can query DBA_REGISTERED_SNAPSHOTS
This table has a column called name which is basically the name of Mview on MVIEW site. Since each MVIEW site can have a different name we cannot compare this column to get list of sites registered for 1 master table.
But we don’t have any master column in this table so we join this table with DBA_SNAPSHOT_LOGS to get list of sites which are registered for a master table

SQL>select a.master, b.name, b.snapshot_site 

from dba_snapshot_logs a, dba_registered_snapshots b

   where a.snapshot_id = b.snapshot_id

   and a.master = 'T';

MASTER       NAME       SNAPSHOT_SITE

------------------------------ -------

T       T_REP       DB1.AMAZON

To check which snapshots has delay

We can use following query to check which snapshots has refresh delay in mins

select a.master, b.name, b.snapshot_site, (sysdate - a.CURRENT_SNAPSHOTS)*24*60 "delay Mins"

from dba_snapshot_logs a, dba_registered_snapshots b

where a.snapshot_id = b.snapshot_id

and (sysdate - a.CURRENT_SNAPSHOTS)*24*60 > &delay;

This will ask for delay and you can enter delay in mins.
After that this will list down all snapshots which are having delay more than what you entered.

Example, if you want to list down all snapshots having delay of more than 10 mins, you need to enter 10.

x$knstmvr

This is another internal table which can be used to check the progress of snapshots.
Columns in this table is self understood.

Following query will provide you the details of snapshot progress

column mvowner format a10 

Column mvname format a30 

column refmode format a8 

column refstate format a12 

column inserts format 99999999 

column updates format 999999999 

column deletes format 999999999 

column event format a30 

column spid format a6 

select  currmvowner_knstmvr mvowner, 

currmvname_knstmvr mvname, 

decode( reftype_knstmvr, 0, 'FAST', 1, 'FAST', 2, 'COMPLETE', REFTYPE_KNSTMVR ) refmode, 

decode(groupstate_knstmvr, 1, 'SETUP', 2, 'INSTANTIATE',3, 'WRAPUP', 'UNKNOWN' ) refstate, 

total_inserts_knstmvr inserts, 

total_updates_knstmvr updates, 

total_deletes_knstmvr deletes, 

b.spid,c.event 

from x$knstmvr X, v$session a, v$process b, v$session_wait c 

WHERE type_knst=6 

and a.paddr = b.addr 

and a.sid = c.sid 

and x.SID_KNST = a.sid 

and x.SERIAL_KNST = a.serial#;

How fast refresh works ?

As we know in case of fast refresh only the changes that happened on master site (or master table) will be applied to MView on target site.
So the changes that happens on master table will be stored in MLOG table created on top of master table.
This is more efficient way than doing complete refresh.
As we know MLOG can be based on primary key or ROWID. ROWID MLOG is uncommon and is not used because if master table gets moved then ROWID will get changed and changes saved in MLOG will be invalid.
So to identify the changes on master table usually primary key based MLOG is created on top of master table.
Only 1 MLOG can be created on master table even if we have multiple sites refreshing from 1 master table.
Before we check on how fast refresh works, lets understand some of the components of fast refresh

MLOG$ table and its important columns

SNAPTIME$$             - This is a date columns and holds the date of 1st Mview refresh time. Example if we have 3 Mviews registered on 1 master table, than this column will hold oldest refresh date among the 3 Mviews
DMLTYPE$$              - This column tells you the type of DML ( U – Update, D – Delete, I – Insert )
OLD_NEW$$             - This column allow the fast-refresh mechanism to distinguish between rows inserted at the mview site and rows with modified primary key values.
CHANGE_VECTOR$$     - Used for subquery and LOB MViews
Apart from above standard columns in MLOG$ table, there will also be primary key columns. These columns are same as primary key columns of master table

AFTER ROW trigger on the master table

From Oracle8 and onward this trigger is kernelized and is no longer visible in the data dictionary. The same trigger can support both ROWID and primary key MViews and will populate the MLOG$_ with the proper values. It also populates the SNAPTIME$$ column (indicating the latest refresh time so far a particular row) and the DMLTYPE$$ column. The snaptime$$ column is populated based on the value of the snaptime column in snap$ table at the MView site and is not updated until the log is first used by a MView refresh.

Registration of Mview on master site

Oracle automatically tries to register a materialized view at its master site or master materialized view site when you create the materialized view, and unregisters when you drop it. The same applies to materialized view groups. Registration of fast refreshable materialized view logs in the master database (SYS.SLOG$) is needed to perform fast refreshes. This information is also used to maintain the materialized view log of the master table.

Fast refresh operation

Fast refresh operation consists of 3 phases

1. Setup Phase
2. Refresh Phase
3. Wrap-up Phase

1) Setup Phase:

Setup has to check if the Mview being refreshed is ROWID based Mview or Primary key based Mview.
After that it has to verify if fast-refresh can be performed for this MView. An MView can perform a fast refresh only if it can use the MView log. This can be determined by checking entry in SYS.SLOG$ table on master site.

DB1>select MASTER, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T'; MASTER                   SNAPID SNAPTIME ------------------------------ ---------- -- T                    15119 2012-10-14:03:01 T                    15120 2012-10-17:09:47

If an entry is present in this table for that SNAPID, then it can be fast refreshed.
Its possible that you have created a complete refreshable MView on some master table which didn’t had MLOG. Later point of time you created MLOG and may be wondering why fast refresh not happening.
You can check the entry in this table and verity. Also in such cases you need to drop and recreate Mview on prebuilt table and make it fast refreshable.
Once its confirmed that fast refresh is possible for MView in question, snaptime$$ column is updated in the MLOG$ table of the altered rows to its own refresh date and time for the first MView that refreshes. This value does not change until the rows are eventually purged from the log.

2) Refresh Operation:

After setup phase, a second check is made to see if fast refresh can be done. This time its the date comparison that is done to ensure their is no mismatch of data.
For fast refresh to work, timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.

How fast refresh work

Lets check out in detail how the date changes happen.
We will keep an eye on 3 tables
1) SYS.SLOG$ on master
2) SYS.MLOG$ on master
3) MLOG$_ on master (This is out MLOG$ table)
I will explain you with live example.
Master table name – T (on DB1 database)
MLOG table name – MLOG$_T (no DB1 database)
We have 2 snapshot sites and each has fast refreshable MView created.
MView Name – T_REP (on DB2 database)
MView Name – T_REP (on DB3 database)
On Master site:

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T'; MASTER                   SNAPSHOT         SNAPID SNAPTIME ------------------------------ ---------------- ---------- ---------------- T                             15119 2012-10-24:01:16 T                             15120 2012-10-24:01:17 Above output represents 2 snapshot site registered on Master table/site DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T'; MASTER OLDEST_PK  YOUNGEST    LOG    MTIME      LAST_PURGE_DATE ------------------------------ ---------------- ---------------- T 2012-10-24:01:16 2012-10-24:01:17 MLOG$_T  2012-10-13:22:25 2012-10-24:01:17

In SYS.MLOG$ table you will have 1 record for each MLOG$ that you create. Since a master table can have only 1 MLOG you will have 1 record for each master table.
There are no records in MLOG$_T table

DB1>select count(1) from MLOG$_T;

  COUNT(1)

----------

     0

Lets make changes in master table T and see what happens in above 3 tables

DB1>update T set DATA_OBJECT_ID = 0 where OBJECT_ID = 2300;

1 row updated.

DB1>commit;

Commit complete.

DB1>

With above modification, no change seen in SYS.SLOG$ table and SYS.MLOG$ table

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T'; MASTER      SNAPSHOT         SNAPID SNAPTIME ------------------------------ ---------------- ---- T                             15119 2012-10-24:01:16 T                             15120 2012-10-24:01:17 DB1> select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T'; MASTER OLDEST_PK    YOUNGEST     LOG      MTIME         LAST_PURGE_DATE ------------------------------ ---------------- ---------------- T   2012-10-24:01:16 2012-10-24:01:17 MLOG$_T  2012-10-13:22:25 2012-10-24:01:17

However we see new entry in MLOG$_T table corresponding to the row changed

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

 OBJECT_ID SNAPTIME$$        D O

---------- ---------------- - -

      2300 4000-01-01:00:00 U U

OBJECT_ID is the primary key column and value in MLOG$_T table represent the row we changed
Observe the value of SNAPTIME$$ – 4000-01-01. This is a date in future which will not be reached in lifetimes. This date tells us that none of the Mview sites has done a fast refresh of this change.
So lets rewind our statement about second check done during “Refresh Phase”
For fast refresh to work, timestamp of oldest/oldest_pk column in mlog$ must be older than the time of last refresh.
It means oldest_pk <= last refresh time on Mview sites
We can check last refresh time of Mviews by checking DBA_SHAPSHOTS table or DBA_SNAPSHOT_REFRESH_TIMES table on Mview site.  But same thing can be checked on master site using SYS.SLOG$ table
We know oldest_pk timestamp – 2012-10-24:01:16
Min(last_refresh time) – 2012-10-24:01:16
So since oldest_pk <= last refresh time on Mview sites, refresh can proceed
If one of the site does a fast refresh we can see that MLOG$_T.SNAPTIME$$ timestamp gets updated to refresh time

DB2>exec dbms_snapshot.refresh('T_REP','F');

PL/SQL procedure successfully completed.

DB2>

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

 OBJECT_ID SNAPTIME$$        D O

---------- ---------------- - -

      2300 2012-10-24:01:52 U U

DB1>

We are yet to refresh second site which is registered for this master table and so the record from MLOG$_T is not deleted.
Also, 1 note with respect to SYS.MLOG$ table

DB1> select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T'; MASTER OLDEST_PK    YOUNGEST     LOG     MTIME         LAST_PURGE_DATE ------------------------------ ---------------- ---------------- T   2012-10-24:01:17 2012-10-24:01:52 MLOG$_T  2012-10-13:22:25 2012-10-24:01:52

YOUNGEST column represent the latest refresh time. If you have 5 sites, the one you refresh latest will have that timestamp updated in YOUNGEST column
OLDEST_PK column represent the oldest refresh time. If you have 5 sites, the one you refresh first will have that timestamp updated in OLDEST_PK column
OLDEST column is used in ROWID based MLOG

3) Wrap-up Phase

In this phase it checks if all Mviews are refreshed and if the changes in MLOG$_T table has gone to all site and if the entries in MLOG$_T table can be purged.
Again Oracle checks dates in above 3 tables to determine which records in MLOG$ table can be purged.

How MLOG$ purge works

Oracle automatically tracks which rows in a MView log have been used during the refreshes of MViews, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple mviews can use the same MView log, rows already used to refresh one MView may still be needed to refresh another MView. Oracle does not delete rows from the log until all MViews have used them. In the wrap-up phase of the refresh process, the master MView log is purged. It deletes all unnecessary rows from the MView log. Rows in the MView log are unnecessary if their refresh timestamps MLOG$_.SNAPTIME$$ are older or equal than the oldest entry in SLOG$.SNAPTIME for this log.
It means rows in MLOG$_ will be deleted whose MLOG$_.SNAPTIME$$ <= min(SYS.SLOG$.SNAPTIME)
Lets take an example
We have a live example going where we have updated 1 record in master table and we have refreshed 1 site (out of 2 sites registered)
Here are the outputs of 2 required tables

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T; OBJECT_ID SNAPTIME$$        D O ---------- ---------------- - - 2300 2012-10-24:01:52 U U DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T'; MASTER                   SNAPSHOT         SNAPID SNAPTIME ------------------------------ ---------------- ---------- ---------------- T                             15119 2012-10-24:01:52 T                             15120 2012-10-24:01:17

As you can see SNAPTIME$$ timestamp has the time when this record was first refresh by any site. If we have 3 sites and 1st site refreshes the records this timestamp will get updated.
But if 2nd site refreshes the record, this timestamp will NOT change. However refresh of second site will change the SNAPTIME column in SYS.SLOG$ table. SNAPTIME column in SYS.SLOG$ table always has the latest refresh time for corresponding site.
So we can see that MLOG$_T.SNAPTIME$$ – 2012-10-24:01:52 (this is the time when 1st site got refreshed)
min(SYS.SLOG$.SNAPTIME) – 2012-10-24:01:17
Since MLOG$_T.SNAPTIME$$ > min(SYS.SLOG$.SNAPTIME), row will not be deleted.
If I refresh 2nd site than SYS.SLOG$.SNAPTIME corresponding to that site will get updated with refresh time and in that case MLOG$_T.SNAPTIME$$ <= MIN(SYS.SLOG$.SNAPTIME). When this condition happens it will delete the record from MLOG$ table.
Logically this means that all the sites registered for this master table is been refreshed.
Lets try to refresh 2nd site. This should purge the record from MLOG. We will also see how the dates are updated in 3 tables on master site
After refreshing second site
No rows in MLOG$_T table

DB1>select OBJECT_ID, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$ from MLOG$_T;

no rows selected

SNAPTIME for second site got updated in SYS.SLOG$ table

DB1>select MASTER, SNAPSHOT, SNAPID, SNAPTIME from SYS.SLOG$ where MASTER = 'T';

MASTER                   SNAPSHOT         SNAPID SNAPTIME

------------------------------ ---------------- ---------- ----------------

T                             15119 2012-10-24:01:52

T                             15120 2012-10-24:02:56

In SYS.MLOG$ table, Previous YOUNGEST became OLDEST_PK and new YOUNGEST is the latest refreshed MView timestamp.
Also, LAST_PURGE_DATE gets updated when a record gets purged in MLOG$_T table

DB1>select MASTER, OLDEST_PK, YOUNGEST, LOG, MTIME, LAST_PURGE_DATE from SYS.MLOG$ where MASTER = 'T';

MASTER OLDEST_PK        YOUNGEST LOG     MTIME   LAST_PURGE_DATE T      2012-10-24:01:52 2012-10-24:02:56 MLOG$_T 2012-10-13:22:25 2012-10-24:02:56