SQL 修改物化视图查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4274681/
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
Modify materialized view query
提问by code990
I need to modify Materialized view query . Is is possible to do the same without droping and recreating it.
我需要修改物化视图查询。是否可以在不删除和重新创建它的情况下执行相同操作。
回答by mcalmeida
No, you cannot alter the query of a materialized view without dropping it.
不，您不能在不删除物化视图的情况下更改它的查询。
The CREATE MATERIALIZED VIEW syntax does not support that feature.
CREATE MATERIALIZED VIEW 语法不支持该功能。
The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways:
ALTER MATERIALIZED VIEW 用于通过以下一种或多种方式修改现有的物化视图：
- To change its storage characteristics
- To change its refresh method, mode, or time
- To alter its structure so that it is a different type of materialized view
- To enable or disable query rewrite
- 更改其存储特性
- 更改其刷新方法、模式或时间
- 改变其结构，使其成为不同类型的物化视图
- 启用或禁用查询重写
See Oracle 12c Release 1 Manual for:
请参阅 Oracle 12c 第 1 版手册了解：
CREATE MATERIALIZED VIEW syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6002.htm#i2145767
ALTER MATERIALIZED VIEW syntax: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_2002.htm#SQLRF00808
CREATE MATERIALIZED VIEW 语法：http: //docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6002.htm#i2145767
ALTER MATERIALIZED VIEW 语法：http: //docs.oracle.com/cd/E16655_01/server.121/e17209/statements_2002.htm#SQLRF00808
回答by MaxH
For conditions where you're not sure whether the MVIEW exists or not (which is what CREATE OR REPLACE is really good for), I use;
对于您不确定 MVIEW 是否存在的情况（这就是 CREATE OR REPLACE 真正适合的情况），我使用；
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW name_of_mview';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -12003 THEN
dbms_output.put_line('MVIEW does not exist, which was somewhat expected');
ELSE
RAISE;
END IF;
END;
/
CREATE MATERIALIZED VIEW name_of_mview ... AS SELECT ...
回答by erbsock
You can leave the mview in place. If your concern is to minimize downtime while the new mview instantiates (because you didn't use a prebuilt table), you can do the following.
您可以将 mview 留在原处。如果您关心的是在新的 mview 实例化时最大限度地减少停机时间（因为您没有使用预建表），您可以执行以下操作。
- Create a new table called mview_1 on prebuilt table
- Once it has been created you can drop the old one
- create or replace view mview as select * from mview_1
- 在预建表上创建一个名为 mview_1 的新表
- 创建后，您可以删除旧的
- 创建或替换视图 mview 为 select * from mview_1
Now, whenever you need to rebuild you will be able to do so with little to no downtime since you can simple point the view to the new table/mview going forward.
现在，无论何时您需要重建，您都可以在几乎没有停机时间的情况下进行重建，因为您可以简单地将视图指向新的表/mview。
回答by StewS2
This may be a new feature in a later Oracle version, but I've found this works nicely:
这可能是更高版本的 Oracle 中的一个新特性，但我发现这很好用：
DROP MATERIALIZED VIEW my_mview PRESERVE TABLE;
CREATE MATERIALIZED VIEW my_mview
ON PREBUILT TABLE ...
回答by Gary Myers
You MIGHTbe able to make use of the existing MV as the source of a prebuilt table for the new MV
您MIGHT能够利用现有的MV作为源预先建立的表为新MV
A lot depends on what you are doing to the query of course. If you are adding a column, for example, you'll need refresh everything to get its new value.
当然，很大程度上取决于您对查询所做的事情。例如，如果您要添加一列，则需要刷新所有内容以获取其新值。
PS. The quick way of turning the existing MV into a table would be partition exchange but watch out for gotchas. Then you manipulate the table to match the new result set and create the new MV based on the manipulated table.
附注。将现有 MV 转换为表的快速方法是分区交换，但要注意陷阱。然后操作该表以匹配新的结果集并基于操作的表创建新的 MV。
