Re: Mview refresh is much slower in 10g

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_at_hotmail.com>:
> 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

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