Solved

Complete Refresh Materialized View

Posted on 2011-09-11
7
752 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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

630 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