是否可以部分刷新 Oracle 中的物化视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1783308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to partially refresh a materialized view in Oracle?
提问by Galghamon
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.
我有一个基于其他物化视图、常规视图以及一些表的非常复杂的 Oracle 视图（我无法“快速刷新”它）。大多数情况下，此视图中的现有记录基于日期并且是“稳定的”，新记录集具有新日期。
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.
有时，我会收到回溯日期。如果我维护一张桌子，我知道那些是什么以及如何处理它们，但我想保持它的“视图”。完全刷新大约需要 30 分钟，但对于任何给定日期只需要 25 秒。
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?
我是否必须废弃视图并使用表格和过程来填充或刷新该表格中的给定日期？
采纳答案by Galghamon
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.
为了其他人谁可能会发现这个问题在未来有用的：你可能也想知道，在Oracle 10g中，刷新分区（或任何MVIEW）将导致甲骨文的问题DELETE，其次是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*/.
如果这是给你的性能问题（像我一样），有一个选项来使用atomic_refresh => false，这将TRUNCATE话INSERT /*+APPEND*/。
回答by Conrad
Partition by dateas in answer 3 (skaffman).
按日期分区，如答案 3 (skaffman) 中所述。
You could just do the refresh of a normal mv(table_refreshedbelow) and than use the exchange keyword i.e.
你可以只刷新一个普通的 mv（table_refreshed下面），然后使用 exchange 关键字，即
ALTER TABLE all_partitions
EXCHANGE PARTITION to_calculate
WITH TABLE table_refreshed
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
回答by Visitor
I have been able to refresh a single partition of a materialized view with partition change tracking.
我已经能够使用分区更改跟踪刷新物化视图的单个分区。
It seems to require that the view is created with REFRESH FAST WITH ROWIDoption and DBMS_MVIEW.REFRESHis called with 'P'method.
似乎需要使用REFRESH FAST WITH ROWID选项创建视图并DBMS_MVIEW.REFRESH使用'P'方法调用视图。
回答by skaffman
You can partition materialized views just as you can with normal tables. Partition your mview by date, and then you can refresh only the required partition.
您可以像对普通表一样对物化视图进行分区。按日期对您的 mview 进行分区，然后您可以只刷新所需的分区。
