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

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!

解决方案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;