2

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?

2 Answers 2

8

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.

4
  • 6
    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. Dec 9, 2011 at 17:22
  • Thats something interesting to know. Thanks.
    – Raihan
    Dec 9, 2011 at 18:05
  • 1
    just to add you can get round this drift using trunc Dec 9, 2011 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); Dec 9, 2011 at 18:34
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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