Oracle - 如何使用FAST REFRESH和JOINS创建物化视图 [英] Oracle - How to create a materialized view with FAST REFRESH and JOINS

问题描述

所以我很确定Oracle支持这个，所以我不知道我做错了什么。此代码的工作原理：

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

如果我在JOIN中添加，则会中断：

  CREATE MATERIALIZED VIEW MV_Test 
 NOLOGGING 
 CACHE 
 BUILD IMMEDIATE 
刷新快速提交
 AS 
 SELECT V. *，P. * FROM TPM_PROJECTVERSION V 
 INNER JOIN TPM_PROJECT P ON P.PROJECTID = V.PROJECTID 
  

现在我得到错误：

ORA-12054：无法设置实体化视图的ON COMMIT刷新属性 / p>

我在TPM_PROJECT和TPM_PROJECTVERSION上创建了物化视图日志。 TPM_PROJECT具有PROJECTID的主键，而TPM_PROJECTVERSION具有（PROJECTID，VERSIONID）的复合主键。这是什么诀窍？我一直在挖掘通过Oracle手册没有用。感谢！

解决方案

首先，从 Oracle数据库数据仓库指南：

仅限具有加入的实体化视图的快速刷新限制

...

• FROM列表中的所有表的Rowids必须出现在查询的SELECT
  列表中。

这意味着您的语句将需要如下所示：

  CREATE MATERIALIZED VIEW MV_Test 
 NOLOGGING 
 CACHE 
 BUILD IMMEDIATE 
 REFRESH FAST ON COMMIT 
 AS 
 SELECT V. *，P. *，V. ROWID为V_ROWID，P.ROWID为P_ROWID 
 FROM TPM_PROJECTVERSION V，
 TPM_PROJECT P 
 WHERE P.PROJECTID = V.PROJECTID 
  

另一个需要注意的重要方面是，您的物化视图日志必须使用rowid 创建为。

以下是一个功能测试场景：

  CREATE TABLE foo（foo NUMBER，CONSTRAINT foo_pk PRIMARY KEY（foo））; 
 
创建物化视图登录foo WITH ROWID; 
 
 CREATE TABLE bar（foo NUMBER，bar NUMBER，CONSTRAINT bar_pk PRIMARY KEY（foo，bar））; 
 
 CREATE MATERIALIZED VIEW登录bar WITH ROWID; 
 
 CREATE MATERIALIZED VIEW foo_bar 
 NOLOGGING 
 CACHE 
 BUILD IMMEDIATE 
刷新快速作为SELECT foo.foo，
 bar.bar， 
 foo.ROWID as foo_rowid，
 bar.ROWID as bar_rowid 
 FROM foo，bar 
 WHERE foo.foo = bar.foo; 
  

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;

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

Now I get the error:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

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!

解决方案

To start with, from the Oracle Database Data Warehousing Guide:

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.

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.

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;

这篇关于Oracle - 如何使用FAST REFRESH和JOINS创建物化视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！