Oracle - How to create a materialized view with FAST REFRESH and JOINS

Question

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!

Answer 1

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; 

Answer 2

Have you tried it without the 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