是否可以部分刷新Oracle中的物化视图? [英] Is it possible to partially refresh a materialized view in Oracle?

问题描述

我有一个非常复杂的Oracle视图，它基于其他实例化视图，常规视图以及某些表(我无法快速刷新"它).大多数情况下，此视图中的现有记录基于日期并且是稳定的"，新记录集具有新日期.

有时，我会收到回溯日期.我知道这些是什么以及如果要维护表时如何处理它们，但是我想保留此视图".完整的刷新大约需要30分钟，但是在任何给定的日期仅需要25秒.

我可以指定只更新实例化视图的一部分(即受影响的日期)吗?

我是否必须取消视图并使用表格和过程来填充或刷新该表格中的给定日期?

解决方案

经过对该问题缺乏答案的更多阅读和判断之后，我得出的结论是，不可能刷新实例化的单个分区视图.

如果您可以提供一个语法示例来证明事实，我将很乐意将您的答案标记为可接受的答案.

对于将来可能会发现此问题有用的其他人:您可能还想知道，在Oracle 10g中，刷新分区(或任何mview)将导致Oracle发出DELETE，然后发出INSERT. /p>

如果这给您带来性能问题(如我)，则可以选择使用atomic_refresh => false，先按TRUNCATE，再按INSERT /*+APPEND*/.

I have a very complex Oracle view based on other materialized views, regular views as well as some tables (I can't "fast refresh" it). Most of the time, existing records in this view are based on a date and are "stable", with new record sets having new dates.

Occasionally, I receive back-dates. I know what those are and how to deal with them if I were maintaining a table, but I would like to keep this a "view". A complete refresh would take around 30 minutes, but it only takes 25 seconds for any given date.

Can I specify that only a portion of a materialized view should be updated (i.e. the affected dates)?

Do I have to scrap the view and use a table and a procedure to populate or refresh a given date in that table?

解决方案

After more reading and judging by the lack of answers to this question, I come come to the conclusion that it is not possible to refresh a single partition of a materialized view.

If you can give a syntax example that proves otherwise, I will happily mark your answer the accepted one.

To others who might find this questions useful in the future: you might also want to know that in Oracle 10g, refreshing a partition (or any mview) will cause Oracle to issue DELETE, followed by INSERT.

If this is giving you performance problems (like me), there is an option to use atomic_refresh => false, which will TRUNCATE, then INSERT /*+APPEND*/.

这篇关于是否可以部分刷新Oracle中的物化视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！