Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

New to this topic - in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by web applications (obviously these are only used when the data involved is not completely time-sensitive, we do nightly refreshes of the materialized views). Note that we are doing Complete, On Demand refreshes on a specified schedule.

The issue I am running into is it seems that Oracle has used some internal process to determine the 'master' table and when DML operations occur on the master, the MV becomes invalid. To be honest we don't really care whether the master table has any inserts, updates, deletes as we just want to completely refresh the MV every night - unfortunately when the MV goes invalid it prevents refreshes.

So my question is can I manually set the master table to something like SYS.DUAL so that the MV never goes invalid or is there another workaround?

Failing this I suppose instead of using MVs I would just schedule a nightly CTAS.

Edit: Added the Oracle note # in comments below - synopsis is that when the MV is in the same database as the master table, DML on master table will cause the MV to go invalid ("Needs Recompile" specifically). Certainly I can schedule a nightly recompile but this doesn't seem elegant.

To downvoter - this is my 2nd question here, could you express what makes the question so flawed so I don't keep losing points in future posts? :)

share|improve this question
2  
DML should not cause a MV to become invalid, nor should it prevent refreshes. The MV would go stale, that doesn't seem to be a problem. Did you perhaps mean DDL? –  Leigh Riffel Sep 2 '11 at 19:41
    
Oracle note ID 264036.1 - this is a local materialized view. –  engil Sep 6 '11 at 21:24
add comment

1 Answer

From the Oracle documentation

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table.1

So you should be able to see what changes are invalidating the MV.

Next you should examine the alert log to see if anything has been logged.

I wonder if you have a complex chain of materialized views? Does one depend on another? Is only one becoming invalid and others are not? Try this query to see what depends on what

dbms_snapshot.get_mv_dependencies( list IN VARCHAR2, deplist OUT VARCHAR2);

I would not recommend your suggested workaround until you understand what changes are invalidating the view.

Edit: Metalink note 264036.1 says:

"This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview. However, the query on MV will not return latest update done in master table unless MV is refreshed."

From the way you are using the Materialized Views I am not sure why you cannot issue a refresh of each view that is marked invalid.

share|improve this answer
    
The OP is probably not using logs as he is only doing complete refreshes. As Leigh comments, DML should not be making an MV invalid. I think the question needs work. –  Jack Douglas Sep 6 '11 at 6:45
    
Oracle note ID 264036.1 - this is a local materialized view. –  engil Sep 6 '11 at 21:24
    
@kevinsky - That's what we are doing now via a scheduled job, but optimally it would not require a nightly refresh - we hae other MVs as well but the master tables are all across a dblink so they don't go invalid - is there any way to forcibly tell Oracle which table to use as the master? –  engil Sep 7 '11 at 15:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.