We have seen Materialized view Concepts – Discussion Series 1 and Materialized view Concepts – Discussion Series 2.
This is the third article about Materialized views.

In this article we are going to discuss “How fast refresh works ?”

 

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$_<table_name> 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$_<master_table> 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>
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$_<table_name>.SNAPTIME$$ are older or equal than the oldest entry in SLOG$.SNAPTIME for this log.

It means rows in MLOG$_<master_table> will be deleted whose MLOG$_<master_table>.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

In next article (probably last one of this discussion series), we will understand problems associated with MView Log, Modifying primary key on master table and what is “I am refresh”.

Hope this helps !!