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

User · Answer

The key checks for FAST REFRESH includes the following:

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to oracle11g