Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Complete Refresh Materialized View

Posted on 2011-09-11
7
Medium Priority
?
759 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:ankitupadhyay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Expert Comment

by:ianmills2002
ID: 36520954
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
 

Author Comment

by:ankitupadhyay
ID: 36524027
We are refreshing materialized every 10 min or sooner.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 36891456
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question