A new or advanced oracle DBA can find here the difference in the materialized view refresh behaviour between 9i and 10g. Previously in 9i the materialized view was truncated and then the new data was inserted. In 10g the materialized view is deleted and then the new data is inserted. when the materialized view table is large then deletes could be slower than the truncate, so you might like to use a different parameter in 10g when refreshing the materialized views to simulate the truncate behaviour.
Below article/discussion contains info about the paramete to use.
Refresh method has been changed.
Previously (<9i) for complete refreshes Oracle did truncate mv and
insert /*+ append */.
Now (10g) it does delete, normal insert.
It is discussed for example here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1569
5764787749
I didn't see in the AskTom article cited where it said that the
refresh mechanism changed from truncate/insert append to
delete and insert when moving from 9i to 10g.
It does say that the if the MV is part of a refresh group, a
complete refresh will be done as delete/insert, rather than
as truncate/append.
The last post in that thread (from Pavel Ruzicka) implies that
the default refresh mechanism did indeed change from 9i->10g,
but that there is a workaround.
I ran a small test to create an MV and do a complete refresh
on both 9.2.0.6 and 10.2.0.1 that confirms this behavior.
From the 9i trace:
PARSING IN CURSOR #15 len=56 dep=1 uid=45 oct=85 lid=45
tim=1141749498683672 hv=68464594 ad='5cad9640'
truncate table "JS001292"."MVTEST_MV" purge snapshot log
END OF STMT
...
INSERT /*+ APPEND */ INTO
"JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT
"MVTEST"."OWNER","MVTEST"."TABLE_NAME","MVTEST"."TABL
ESPACE_NAME" FROM "MVTEST" "MVTEST"
END OF STMT
From the 10g trace:
PARSING IN CURSOR #15 len=35 dep=1 uid=56 oct=7 lid=56
tim=1141749792514219 hv=540326182 ad='733ed2d0'
delete from "JS001292"."MVTEST_MV"
END OF STMT
...
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
"JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT
"MVTEST"."OWNER","MVTEST"."TABLE_NAME
","MVTEST"."TABLESPACE_NAME" FROM "MVTEST" "MVTEST"
By changing the refresh method to set atomic_refresh = false, the
truncate/append
behavior can be restored.
begin
-- dbms_mview.refresh('MVTEST_MV',method => 'C');
-- use this with 10g to return to truncate/append behavior
dbms_mview.refresh('MVTEST_MV',method => 'C', atomic_refresh=>false);
end;
/
Here's the results of doing so in 10g:
PARSING IN CURSOR #24 len=57 dep=1 uid=56 oct=85 lid=56
tim=1141750173641027 hv=455978900 ad='705be890'
truncate table "JS001292"."MVTEST_MV" purge snapshot log
END OF STMT
...
PARSING IN CURSOR #24 len=208 dep=1 uid=56 oct=2 lid=56
tim=1141750173945788 hv=896677336 ad='6d21e8f0'
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO
"JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT
"MVTEST"."OWNER","MVTEST"."TA
BLE_NAME","MVTEST"."TABLESPACE_NAME" FROM "MVTEST" "MVTEST"
END OF STMT
