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.
Femme Fatale
  • 870
  • 7
  • 27
  • 56
  • 1
    This would be a very good question for http://dba.stackexchange.com/ but I can't figure out how to vote to move there (it's not an option). – Josh Smeaton Dec 08 '11 at 23:05

2 Answers2

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).

Community
  • 1
  • 1
Josh Smeaton
  • 47,939
  • 24
  • 129
  • 164
  • 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. – Femme Fatale Dec 09 '11 at 00:52
  • 2
    @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. – Josh Smeaton Dec 09 '11 at 01:24
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!

c0deNinja
  • 3,956
  • 1
  • 29
  • 45