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 depicts (how i am going to change it)
  2. What is DISABLE QUERY REWRITE
    In other words DISABLE QUERY REWRITE vs ENABLE QUERY REWRITE with respect to materialized view.
Improve this question
1
  • 1
    This would be a very good question for dba.stackexchange.com but I can't figure out how to vote to move there (it's not an option). Commented Dec 8, 2011 at 23:05

2 Answers 2

Reset to default
4

Materialized Views in oracle support a feature called Query Rewriting. This means that the database can analyse a particular query to the base tables, decide whether the same results would be returned from the materialized view, and query the MV instead of the base tables. This can be quite a good optimisation in some cases. Telling oracle to disable query rewrites means to forego this potential optimisation, and always query the base tables even if a query to the MV would return the same data.

Example would be:

create materialized view emp_salary refresh fast on commit as select first_name, last_name, salary from employee, pay_rate where employee.id = pay_rate.employee_id ;

Then executing a query:

 select last_name, salary from employee, pay_rate where employee.id = pay_rate.employee_id

The query engine could take the regular select statement above, and retrieve the data directly from the materialized view, without having to do a potentially expensive join (since the join is already done by the MV). This is query rewriting.

This question describes what the with sysdate next clause does. Apparently, it tells the database that the next refresh date is going to be in 1 day (sysdate +1).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

But if i query "select * from emp_salary" it will bring the same result as "select last_name, salary from employee, pay_rate where employee.id = pay_rate.employee_id" without even enabling the query rewriting.
@ShahJee correct, but then you need to change all your application code to use the new materialized view. Query rewriting allows your applications to execute more efficient queries without changing the application. This way, no one needs to know about the MV. They just sit there, being used when appropriate. DBAs can now write more efficient queries that applications can use, without knowing they even exist.
1

one of the main advantages of materialized views is the ability to use query rewrite... not really sure why you would disable query rewrite.

query rewrite allows the materialized views to be used instead of the actual detail tables.

the NEXT value is used by oracle to determine the first automatic refresh. so in your example it will be refreshed for the first time 1 day after creation and refreshed everyday from then on.

change to SYSDATE + 6/24 to run every 6 hours!

Improve this answer

2 Comments

what would be sytax if i want to change the refresh time to 6 hourly.
i think you can just do sysdate + 6/24 instead of sysdate + 1

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.