Oracle - 如何使用 FAST REFRESH 和 JOINS 创建物化视图

2021-12-06 00:00:00 sql database oracle11g oracle

所以我很确定 Oracle 支持这一点，所以我不知道我做错了什么.此代码有效:

So I'm pretty sure Oracle supports this, so I have no idea what I'm doing wrong. This code works:

CREATE MATERIALIZED VIEW MV_Test
  NOLOGGING
  CACHE
  BUILD IMMEDIATE 
  REFRESH FAST ON COMMIT 
  AS
    SELECT V.* FROM TPM_PROJECTVERSION V;

如果我添加一个 JOIN，它会中断:

If I add in a JOIN, it breaks:

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

现在出现错误:

ORA-12054:无法为物化视图设置 ON COMMIT 刷新属性

我在 TPM_PROJECT 和 TPM_PROJECTVERSION 上都创建了物化视图日志.TPM_PROJECT 的主键为 PROJECTID，TPM_PROJECTVERSION 的复合主键为 (PROJECTID,VERSIONID).这有什么诀窍?我一直在翻阅 Oracle 手册，但无济于事.谢谢！

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!

推荐答案

先从 Oracle 数据库数据仓库指南:

仅具有联接的物化视图的快速刷新限制

...

  • FROM 列表中所有表的 Rowids 必须出现在 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

另一个需要注意的关键方面是，您的物化视图日志必须创建为 with rowid.

Another key aspect to note is that your materialized view logs must be created as with rowid.

以下是功能测试场景:

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;

相关文章