Mview refresh is much slower in 10g

From: David Boyd <davidb158_at_hotmail.com>
Date: Wed, 17 Jan 2007 10:29:55 -0500
Message-ID: <BAY101-F2995D90BEF4041D26658F2EFAB0@phx.gbl>

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 use exec 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

---

Get live scores and news about your team: Add the Live.com Football Page www.live.com/?addtemplate=football&icid=T001MSN30A0701

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 17 2007 - 09:29:55 CST