Home » SQL & PL/SQL » SQL & PL/SQL » Materialized views (Oracle 9i)
Materialized views [message #342542] Fri, 22 August 2008 13:04 Go to next message
Messages: 1
Registered: July 2008
Location: Hyderabad
Junior Member
Hi All,

I have a Materialized views Group, on this group I have 57 MVs these are all created on Master data (OLAP) log files and we have dependency schedule to run this jobs. Load dependency is like (MD tabs Then  Log files MVs) this is not a problem with us.

For 57 views to get updated (refreshed) around 4 hours in these views one view is taking nearly 2 hours to update. We are updating this group using shell script as Batch dependency.

Refresh type: Force complete
On demand
In this view we have simple select statement only and it is contains nearly 10 million records as this is taking more than two hours to update and reaming 56 views are taking 1 hour only.

Here my question is.

For reduce the Batch time, we want divide the group into 2 groups and trying to run simultaneously (we want to change Shell script).

Can you please let me know is this working fine or not?

Hope I would get valid advises from DB experts.

Re: Materialized views [message #342544 is a reply to message #342542] Fri, 22 August 2008 13:31 Go to previous messageGo to next message
Messages: 25297
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow posting guidelines as stated in URL above
Re: Materialized views [message #342746 is a reply to message #342542] Sun, 24 August 2008 17:46 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Let me ask this question:

Q: Why are these 57 MVIEWS part of one refresh group?
A: The original designer wanted them to behave as if they were one MVIEW not 57. Thus he wanted the state of all 57 to be the same before and after the refresh.

If this is a requirement then splitting the refresh group is a bad thing because it opens the door for some of your MVIEWS to refresh but not others. This can be a nightmare in some circumstances. So you should find out why all 57 were originally put into one refresh group.

This said, splitting the group up so that you can do concurrent refrehes can reduce over batch time.

As an alternative you might to try and get the MVIEWS to refresh in parallel. Similar potential effect as splitting the refreshg roup into pieces, but you do not have to split up the refresh group.

Another alternative would be to try and tune the MVIEW that is taking the most time. The time taken is most likely one of two things: a) query exection takes a long time, b) moving data across the network is taking a long time. If A) then you can tune the MVIEW, if B) then splitting the workload can help to the point that your network approaches saturation.

Good luck, Kevin
Previous Topic: SQL%ROWCOUNT don't give the true value !!!
Next Topic: what's the effective ways for data existence check
Goto Forum:

Current Time: Sun Mar 26 17:55:09 CDT 2017

Total time taken to generate the page: 0.12699 seconds