物化视图 - Oracle

发布于 2024-12-20 05:34:50 字数 266 浏览 1 评论 0原文

我有一个物化视图,我想改变它的刷新时间:

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

我想知道一些事情。

  1. NEXT SYSDATE +1 描述了什么(我将如何更改它)
  2. 什么是 DISABLE QUERY REWRITE
    换句话说,对于物化视图,禁用查询重写与启用查询重写。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

痴意少年 2024-12-27 05:34:50

oracle 中的物化视图支持称为查询重写的功能。这意味着数据库可以分析对基表的特定查询,决定是否从物化视图返回相同的结果,并查询 MV 而不是基表。在某些情况下这可能是一个很好的优化。告诉 Oracle 禁用查询重写意味着放弃这种潜在的优化,并且始终查询基表,即使对 MV 的查询将返回相同的数据。

示例如下:

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
;

然后执行查询:

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

查询引擎可以采用上面的常规 select 语句,并直接从物化视图检索数据,而无需执行可能昂贵的联接(因为联接已由 MV 完成)。这是查询重写。

这个问题描述了with sysdate next子句的作用。显然,它告诉数据库下一次刷新日期将在 1 天后 (sysdate +1)。

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

讽刺将军 2024-12-27 05:34:50

物化视图的主要优点之一是能够使用查询重写...不太确定为什么要禁用查询重写。

查询重写允许使用物化视图而不是实际的详细表。

Oracle 使用 NEXT 值来确定第一次自动刷新。因此,在您的示例中,它将在创建后 1 天首次刷新,并从那时起每天刷新。

更改为 SYSDATE + 6/24 每 6 小时运行一次!

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!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文