CODEWITHSUNDEEP
sqloracle-databasematerialized-views
Femme Fatale
Femme Fatale

Reputation: 880

Materialized View - Oracle

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.

Upvotes: 2

Views: 1796

Answers (2)

Josh Smeaton
Josh Smeaton

Reputation: 48710

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

Upvotes: 4

c0deNinja
c0deNinja

Reputation: 3986

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!

Upvotes: 1

Related Questions