Thanks very much for your info. It helps me to explain why the refresh is much slower in 10g. I?ll create a job to refresh it.
Dave
From: "Gints Plivna" <gints.plivna@xxxxxxxxx> Reply-To: gints.plivna@xxxxxxxxx To: davidb158@xxxxxxxxxxx CC: oracle-l@xxxxxxxxxxxxx Subject: Re: Mview refresh is much slower in 10g Date: Wed, 17 Jan 2007 17:54:07 +0200 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:15695764787749 Gints Plivna http://www.gplivna.eu 2007/1/17, David Boyd <davidb158@xxxxxxxxxxx>:Hi List, Recently we upgraded our database from 9.2.0.6 to 10.2.0.2. We have a materialized view that joins two tables in the same database. It takes 19 min to refresh in 9i, but it takes more than 1 hr in 10g. The select statement that creates the mview takes 300msec in 9i and 400msec in 10g.Our system is Solaris 9 for Oracle 9i and Solaris 10 for Oracle 10g. We useexec dbms_refresh.refresh('"my_schema"."TEST_MV"') to refresh the mview. Please advise how to speed up the refresh in 10g. Also do we have to regenerate stats every time after we refresh the mview? Dave-- //www.freelists.org/webpage/oracle-l
_________________________________________________________________From photos to predictions, The MSN Entertainment Guide to Golden Globes has it all. http://tv.msn.com/tv/globes2007/?icid=nctagline1
-- //www.freelists.org/webpage/oracle-l