Solved

Complete Refresh Materialized View

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

680 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