Complete Refresh Materialized View

Hi,
I have created a Materialized View that has some complex logic.
So I was not able to create it as REFRESH FAST ON COMMIT. And had to create it as REFRESH COMPLETE ON DEMAND.
But I have read that during the refresh, MV would be unavailable to query.
I want to know if I can
-- make it as Refresh FAST on Commit
-- Or somehow solve the problem of unavailability

We use Oracle 10g.
Attached is the script for Materialized view.
ankitupadhyayAsked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
I don't see an attached script with the details of your materialized view, but your description of the problem is clear enough.  No, you and I don't get to re-write Oracle's rules for which materialized views can support a REFRESH FAST and which ones need a REFRESH COMPLETE.  If your view includes complex logic, it likely will need a REFRESH COMPLETE. And yes, during a REFRESH COMPLETE the view will be unavailable for queries.

Materialized views don't solve all possible reporting problems, they just help solve some of them, but they do have some limitations.  The suggestion to create a "temporary" materialized view (or table) that can be used instead of the actual materialized view during the refresh looks like a good idea to me.  You would need to do a couple "rename table..." commands to make this work, and if anyone has a query currently open against the materilaized view, that could be a problem.

Another option would be to consider using a reporting table instead of an actual materilized view, since this gives you more flexibility to control how and when it gets refreshed.
0
 
ianmills2002Commented:
At my work, we don't completely get rid of the problem of the Materialized view becoming unavailable, but we mange to minimise the time that it is unavailable.

We have over 25 Materialized views and process them each night the same way. We create a temporary Materialized view from the view or query, when that is complete we then drop the live Materialized view and recreate it based on the new Materialized view.

Most of our Materialized views are only unavailable for seconds at a time. The largest is about 2 minutes only because it contains over 13M rows.

Regards,
Ian
0
 
ankitupadhyayAuthor Commented:
We are refreshing materialized every 10 min or sooner.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.