fast refresh of single-table materialized views – algorithm summary

Today we are going to investigate how Oracle fast refreshes materialized views (MVs) of a single master table, containing no aggregate but, at most, filter predicates and additional column definitions:

create materialized view test_mv

build immediate

refresh fast on demand

with rowid

-- with primary key

as

select test_t1.*, x1+x2 as x1x2

from test_t1

where x1 != 0.42;

This kind of MVs might be considered a degenerate case of a join-only MV, a topic that we investigated in an earlier post, and one could expect the same algorithm. But that is not the case: the test case shows that the algorithm used is very different.

The two main differences are (as we are going to illustrate in detail) that UPDATEs are actually used in this case (as noted by Cristian Cudizio) instead of DELETE+INSERT only, and especially that row-by-row propagation is performed instead of using a couple of single SQL statements.

This kind of MV is frequently used for replication across a db-link (with a clause such as “from test_t1@db_link”); in this scenario, the MV used to be named SNAPSHOT in old releases. I have checked this scenario as well (not included in the test case) and the only difference is that, obviously, the master table test_t1 is referenced via a db-link and a few hints are injected by the refreshing engine.

In the test case, I have checked both the WITH ROWID and WITH PRIMARY KEY options for the MV DDL; the algorithm turns out as being identical, besides (obviously) that in the former the rowid and in the latter the primary key is used to identify rows.

I am going to follow the path of the previous discussion about join-only MVs referenced above, as both the test case format and some of the actual refresh steps are very similar. I have tested on 9.2.0.8, 10.2.0.4 and 11.1.0.7 for the most common DML on the base table (conventional INSERTs, UPDATEs and DELETEs). I have seen no difference in the algorithm for the three kernel versions.

Materialized view logs configuration

Even for this test case, I have configured the materialized view logs to “log everything” to check whether Oracle is able to take advantage of more information in the log:

create materialized view log on test_t1

with sequence, rowid, primary key (x1, x2)

including new values;

but even for single-table MVs the algorithm uses only the rowid or primary key information, hence the minimal (and hence optimal) log configuration is, for the WITH ROWID option:

create materialized view log on test_t1 with rowid;

and for the WITH PRIMARY KEY option:

create materialized view log on test_t1 with primary key;

Log snapshots

The first step in the refresh algorithm is to take a log snapshot, exactly as in the join-only case, by setting snaptime$$ = current time. Hence the marked log rows (the ones and only ones to consider for propagation) will be characterized by snaptime$$ < = current time and > last snapshot refresh time. See the previous post about the join-only case for a more in-depth discussion.

Note: actually, for the sake of precision, two (minor) differences with the join-only case are that the snapshot statement is exactly the same in all versions (there’s no special version for 11.1.0.7) and that the log is not “inspected to count the number and type of the logged modifications”.

Core algorithm: the DELETE and UPSERT steps

Then, the core replication starts. The propagation from the master table is composed of two simple steps, steps that I’ve named DELETE and UPSERT (UPDate + insERT).

The first DELETE step is a simple select-then-delete row-by-row processing, where each row returned by a select statement is passed to a single-row delete statement.

For the WITH ROWID option, the select statement of the DELETE step is (editing for readability: removing hints, unnecessary aliases, etc):

select distinct m_row$$

from (

select m_row$$

from mlog$_test_t1

where snaptime$$ > :1

and dmltype$$ != 'I'

) log

where m_row$$ not in

(

select rowid from test_t1 mas

where (mas.x1 <> 0.42)

and mas.rowid = log.m_row$$

);

and the delete is a trivial

delete from test_mv where m_row$$ = :1;

The select+delete purpose is to delete all marked rows that are not in the master table anymore, or that are still there but that do not satisfy the MV defining SQL (here, x1 != 0.42) anymore.

Page 1 of 3 | Next page