Solved

Materialized View Log for Multiple Materialized Views

Posted on 2013-01-09
6
701 Views
Last Modified: 2013-01-11
Environment:
HPUX 11.11
Oracle 9.2.0.7

I don't have a lot of experience using Materialized Views so I'd like some clarification if possible.

I have a couple of Views that users use heavily that represent self joining a table to itself from 6 to 10 times.

When called in a query, these views are adding some considerable overhead to the query run time.

Based on the answer to a previous question, I'm looking to turn them into Materialized Views.

Because they would be MV's based upon a self join, they are not considered "simple" MV's and can't be used for FAST REFRESH.  

Can multiple MV's be kept updated from a single MV Log ?  Will Oracle keep the updates in sync in multiple target MV's that are not simple and have to do a complete refresh ?

In the Oracle documentation (here), under the Managing Materialized View Log Space header, it talks about " multiple simple materialized views".

Since my MV's aren't "simple", is there anything I need to be aware of ?

I don't *think* there's going to be a concern, but would like to be sure before I try to implement.

Anyone have any experience with this kind of thing ?

Thanks in advance for any insight.
0
Comment
Question by:Steve Wales
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 38761688
instead of creating multiple simple materialized views, create  a materialized view log for each mv ... this will make the mv refresh to check out their independent logs while refreshing..

mv log is specified at the time of creation of mv

for ex:

CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID (dept_id, emp_id, ename,startdate) INCLUDING NEW VALUES;
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38762944
Materialized view logs are not used for complete refreshes.  They are only used for fast refreshes.  Since your views are not "simple" and require a complete refresh every time, there is no need to create the materialized view log.
0
 
LVL 22

Author Comment

by:Steve Wales
ID: 38763204
There's no way to tell the materialized view to not refresh unless there has a been a change to the underlying data when using a complex MV ?

The structure I'm dealing with doesn't change very often.  It seems like such a waste to rebuild the complete structure every hour when it might only change once a quarter if that.

My last part to this question, then, is what happens if a query attempts to execute against the MV while a rebuild is in progress (or vice versa, a rebuild starts while a query is running)?

I'm hoping that it will just hold a lock while the first process does it's thing and the second process to start will wait until the first operation completes and then complete successfully.
0
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.

 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 38763281
This may have changed in 11 as I have not checked yet, but on a complete refresh, while the refresh is in process the table will appear empty to all users.  The reason is that the first thing a refresh of a complex materialized view does is truncate the table.

What I have seen done if you want it to always be accessible is to actually have 2 materialized views and a synonym.  The synonym points to the current materialized view.  You refresh the other view and once it completes successfully change the synonym to the one that just refreshed.  Users access the synonym and the fact that there is 2 views is hidden to them.
0
 
LVL 22

Author Comment

by:Steve Wales
ID: 38763354
OK maybe I can look at it that way.  It doesn't take long for the refresh to complete, a matter of a few seconds, but because of Murphy's Law, you know that the users will want to query it in that short window.

Would need to modify the job that does the refresh to play synonym dancing, but that's doable.

I'll play with it and get back to you.  Thanks.
0
 
LVL 22

Author Closing Comment

by:Steve Wales
ID: 38769363
Thanks.  This is how they want to proceed, so we'll go that way.
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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now