Solved

Complete Refresh Materialized View

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2016 Databse move 5 56
Steps to produce a data strategy 5 51
SQL Server memory sizing - reallocation 16 74
error in my cursor 5 36
This article describes some very basic things about SQL Server filegroups.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

825 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