I wouldn't recommend two materialized views cause if it takes three hours to refresh one, you're up to six hours total when you refresh two of them. I'd suggest a slightly different option.
1) Create a public synonym pointed toward orig_mview. Use this for queries, etc, name it identical to the existing mview.
2)Do a create table as (CTAS). Create table temp1 as select * from orig_mview;
3) alter your synonym to point to the temp1 table.
4) rebuild the mview
5) point the synonym back to the mview and trunc the temp1 table.
CTAS is extrememly fast, it should only take a couple of minutes, even for a large table. This lets you have the appearance of being up continuosly while you refresh behind the scenes.
-Wes
Main Topics
Browse All Topics
by: cybottoPosted on 2002-11-08 at 08:02:55ID: 7425238
A way around is to have two materialized views, so when one m-view is refreshed the queries are accessing the other view.
Another table with a column which holds the name of the actual m-view and a function which returns the name of the m-view and insert it into dynamic SQL.