are fast refreshes really fast?

I've been working with Materialized Views and fast refreshes lately. I finally stopped to think about what is going on. Amazing what happens when I stop to think. When I think of fast refreshes, I assume the materialized view is refreshed from the MLOG$ tables associated with the base tables (MLOG$ tables record changes, Insert/Update/Delete, made to the base tables). I just realized that this isn't necessarily so. A fast refresh can do a full table scan (or index scan, ...) on base tables. Pretty straightforward, I just never stopped to think. I guess this means that some fast refreshes can take quite a while (the pause that refreshes?).

Here is the deal. If the materialized view is based on a join there is no choice but to access the base table(s). A simple example:

CREATE TABLE test1 (id1 number);
CREATE TABLE test2 (id2 number

                                   id1 number);

CREATE MATERIALIZED VIEW LOG on test1
WITH ROWID;

	this creates mlog$_test1
	there are some problems with join fast refreshes using Primary Keys

CREATE MATERIALIZED VIEW LOG on test2
WITH ROWID;
        this creates mlog$_test2

CREATE MATERIALIZED VIEW test1_test2
WITH ROWID
AS
SELECT test1.rowid "t1_rowid", test2.rowid "t2_rowid", test1.id1, test2.id2 FROM test1, test2
WHERE test1.id1=test2.id1
/

INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (2,1);
COMMIT;
        mlog$_test1 and mlog$_test2 both have a single entry.

exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');

        do a fast refresh

So far this seems OK. All that is needed to populate the materialized view are the mlogs. After the refresh completes successfully, the two mlog$ tables are empty, as expected.

Now try

INSERT INTO test2 VALUES (3,1);
COMMIT;
        mlog$_test2 has one row, mlog$_test1 is empty.

When I refresh test1_test2, a row is added because of my entry into test2. The test1 data, however, is no longer in mlog$_test1, but in the base table test1. The fast refresh must access the base tables. How it does so is dependent on available indexes, statistics, table size, ...

To confirm this, I ran my test with 10046 trace on, and did three sets of inserts/fast refresh

case#1
INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (2,1);
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');

The trace file had two different INSERT INTO test1_test2 statements. It was actually formed, by Oracle, with various hints and a nested subquery. The main point is that one of the inserts was driven by MLOG$_TEST2 and accessed test1 (by index unique scan in this case), while the other insert was driven by MLOG$_TEST1 and accessed base table test2 (fts/hash join).

case#2
INSERT INTO test2 values (3,1);
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');

The trace file had a single INSERT INTO test1_test2. It was driven by MLOG$_TEST2 and accessed test1 with index unique scan.

case#3
INSERT INTO test1 VALUES (2);
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');

The trace file had a single INSERT INTO test1_test2. It was driven by MLOG$_TEST1 and accessed test2 with fts/hash join.

I am still looking through my trace files when I have free time (ha!) to see if there is any other good stuff. For example, the explain plans for the INSERT INTO test1_test2 inlude a view VW_NSO_1 which I can't find. No clue Received on Wed Jul 16 2003 - 11:35:34 CDT