Re: materialized view refresh

sudhad_2000_at_yahoo.com (sudhakar) wrote in message news:<b381ffc7.0306070150.574b0036_at_posting.google.com>...
> Hi,
> I need a solution on materialized view refresh.
> when user A (Session A)is accessing materialized view MV1. The refresh
> method we are using here is "REFRESH FORCE". Oracle job from the
> session (Session B) issues a materialized view refresh. This refresh
> removes data from MV1 and then refreshes (complete refresh) the MV1.
> Please note that MV1 is on Oracle 9i. Master tables of MV1 are on
> Oracle 8i. The query is a complex query.
>
> If I put "REFRESH FAST" with in the create script I get the following
> error
> ORA-12015: cannot create a fast refresh materialized view from a
> complex query
>
> I need a solution that does mv1 refresh in a transaction. That is,
> when "Session B" is refreshing mv1, "Session A" should still be
> accessing old data.
>
> Regards,
> Sudhakar

This is a very common problem and different people have different ways of dealing with it, depends on what is possible at your site. You need to refresh your warehouse, but you want the warehouse to be up during the refresh. This is often not possible and the best you can try to accomplish is to reduce the downtime. As long as you are using complete refresh, the downtime will be long - unless you have backup copy of the MV. Possibly you can create a table out of the MV, user access the table while MV is being refreshed. When the refresh is done, exchange the table with the MV (mindful of the user access in the meantime). New data will be available to the users much quickly. Of course a sound design of the mechanism is the key.

• Jusung Yang