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.

I have materialized view for which i want to alter its refresh time:

REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE 

I want to know few things in this.

  1. What does NEXT SYSDATE +1 depict?
  2. How am I going to change it (e.g after every 6 hours)?
  3. What is DISABLE QUERY REWRITE?
share|improve this question
add comment

2 Answers

NEXT SYSDATE +1 means the materialized view will be automatically refreshed every day at the same time when the view is created.

In order to refresh the materialized view every 6 hours use NEXT SYSDATE +6/24

DISABLE QUERY REWRITE indicates that the materialized view is not eligible to be used by the query optimizer to rewrite sql queries on the base tables.

share|improve this answer
2  
SYSDATE + 1 tells the materialized view to start refreshing 24 hours after it finishes refreshing the last time. If it takes 5 minutes to refresh, that means that the next refresh will be 5 minutes later the next day and the refresh time will slowly drift by 5 minutes a day. This tends to catch folks out when they find that the materialized view that was set to refresh at 3am a couple months ago is now refreshing at 9am right when users are pounding the system. –  Justin Cave Dec 9 '11 at 17:22
    
Thats something interesting to know. Thanks. –  Raihan Dec 9 '11 at 18:05
    
just to add you can get round this drift using trunc –  Jack Douglas Dec 9 '11 at 18:31
    
Here is an example for six hours: create materialized view m1 refresh complete next trunc(sysdate) + ((trunc(to_char(sysdate,'HH24')/6)*6)+6)/24 as (select f1 from t1); –  Leigh Riffel Dec 9 '11 at 18:34
add comment

To support Raihan's correct answer+1, here are some excerpts from the 11.2 SQL Language Reference.

START WITH Clause
Specify START WITH date to indicate a date for the first automatic refresh time.

NEXT Clause
Specify NEXT to indicate a date expression for calculating the interval between automatic refreshes.

QUERY REWRITE Clause
Specify DISABLE if you do not want the materialized view to be eligible for use by query rewrite.

share|improve this answer
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.