vw_nso_1 is probably an inline view .... look for statements in the trace file ... something that has inline views ..
Thanks for this analysis ...
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-----Original Message-----
From: Henry Poras [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 16, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L
Subject: 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
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).
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
