SQL Oracle - 如何使用 FAST REFRESH 和 JOINS 创建物化视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7505020/
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
Oracle - How to create a materialized view with FAST REFRESH and JOINS
提问by Mike Christensen
So I'm pretty sure Oracle supports this, so I have no idea what I'm doing wrong. This code works:
所以我很确定 Oracle 支持这个，所以我不知道我做错了什么。此代码有效：
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.* FROM TPM_PROJECTVERSION V;
If I add in a JOIN, it breaks:
如果我添加 JOIN，它会中断：
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.*, P.* FROM TPM_PROJECTVERSION V
INNER JOIN TPM_PROJECT P ON P.PROJECTID = V.PROJECTID
Now I get the error:
现在我得到错误：
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
ORA-12054: 无法为物化视图设置 ON COMMIT 刷新属性
I've created materialized view logs on both TPM_PROJECT and TPM_PROJECTVERSION. TPM_PROJECT has a primary key of PROJECTID and TPM_PROJECTVERSION has a compound primary key of (PROJECTID,VERSIONID). What's the trick to this? I've been digging through Oracle manuals to no avail. Thanks!
我已经在 TPM_PROJECT 和 TPM_PROJECTVERSION 上创建了物化视图日志。TPM_PROJECT 的主键为 PROJECTID，TPM_PROJECTVERSION 的复合主键为 (PROJECTID,VERSIONID)。这有什么诀窍？我一直在翻阅 Oracle 手册，但无济于事。谢谢！
回答by Allan
To start with, from the Oracle Database Data Warehousing Guide:
首先，来自Oracle 数据库数据仓库指南：
Restrictions on Fast Refresh on Materialized Views with Joins Only
...
- Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
仅具有联接的物化视图的快速刷新限制
...
- FROM 列表中所有表的 Rowid 必须出现在查询的 SELECT 列表中。
This means that your statement will need to look something like this:
这意味着您的语句需要如下所示：
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.*, P.*, V.ROWID as V_ROWID, P.ROWID as P_ROWID
FROM TPM_PROJECTVERSION V,
TPM_PROJECT P
WHERE P.PROJECTID = V.PROJECTID
Another key aspect to note is that your materialized view logs must be created as with rowid.
另一个需要注意的关键方面是您的物化视图日志必须创建为with rowid.
Below is a functional test scenario:
下面是一个功能测试场景：
CREATE TABLE foo(foo NUMBER, CONSTRAINT foo_pk PRIMARY KEY(foo));
CREATE MATERIALIZED VIEW LOG ON foo WITH ROWID;
CREATE TABLE bar(foo NUMBER, bar NUMBER, CONSTRAINT bar_pk PRIMARY KEY(foo, bar));
CREATE MATERIALIZED VIEW LOG ON bar WITH ROWID;
CREATE MATERIALIZED VIEW foo_bar
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS SELECT foo.foo,
bar.bar,
foo.ROWID AS foo_rowid,
bar.ROWID AS bar_rowid
FROM foo, bar
WHERE foo.foo = bar.foo;
回答by steve godfrey
Have you tried it without the ANSI join ?
你试过没有 ANSI join 吗？
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT V.*, P.* FROM TPM_PROJECTVERSION V,TPM_PROJECT P
WHERE P.PROJECTID = V.PROJECTID
回答by Paul
You will get the error on REFRESH_FAST, if you do not create materialized view logs for the master table(s) the query is referring to. If anyone is not familiar with materialized views or using it for the first time, the better way is to use oracle sqldeveloper and graphically put in the options, and the errors also provide much better sense.
如果您没有为查询所引用的主表创建物化视图日志，您将在 REFRESH_FAST 上收到错误消息。如果有人不熟悉物化视图或第一次使用它，更好的方法是使用oracle sqldeveloper并以图形方式放入选项，并且错误也提供了更好的意义。
回答by pahariayogi
The key checks for FAST REFRESH includes the following:
FAST REFRESH 的关键检查包括以下内容：
1) An Oracle materialized view log must be present for each base table.
2) The RowIDs of all the base tables must appear in the SELECT list of the MVIEW query definition.
3) If there are outer joins, unique constraints must be placed on the join columns of the inner table.
No 3 is easy to miss and worth highlighting here
No 3 很容易错过，值得在这里强调
