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
what that is.
Hope this helps someone.
Henry
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
