36

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!

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
  • 2
    Did you try `DBMS_MVIEW.EXPLAIN_MVIEW`? It should show you available options for the view? – a1ex07 Sep 21 '11 at 20:05
  • First thing I'd do is lose the *'s in the select clause. Not sure how the view would handle duplicate column names – Phil Sep 21 '11 at 20:10
  • Yea I've been trying to figure out how to run DBMS_MVIEW.EXPLAIN_MVIEW but haven't gotten it to work yet - Sorry, I'm very new to Oracle. – Mike Christensen Sep 21 '11 at 20:43

5 Answers5

48

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;
Allan
  • 17,141
  • 4
  • 52
  • 69
  • 2
    Uggghhhh holy crap thank you! That FINALLY worked. I actually don't really get the point of materialized views in Oracle. They're limited to the point where they're not really useful to anyone. I wonder if Indexed Views in MS SQL are any better. – Mike Christensen Sep 21 '11 at 20:11
  • 2
    Indexed views in MS SQL are different - they're always updated immediately, but there are some quite severe limitations, like no sub-queries, no outer joins, no nested indexed views, no non-deterministic functions etc. See https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views – Reversed Engineer Sep 21 '17 at 12:21
  • I have in the past addressed the performance of MVs by using the prebuilt table option. I am not sure that actually will address the concern shown. The prebuilt tables MV can be indexed etc. to improve performance. Gonna give it a try though. – Allen Aug 26 '22 at 11:28
6

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
steve godfrey
  • 1,234
  • 7
  • 14
  • 1
    Hmm this seems to magically work better. However, now I get an error about the REFRESH FAST. If I change it to REFRESH COMPLETE, it creates the view. This might make for some very slow updates if it has to manually run that whole query every time any of the underlying tables are updated. I though FAST REFRESH was allowed so long as there were materialized view logs for each table. – Mike Christensen Sep 21 '11 at 19:41
  • In deed, some time ago I created a case for that at Oracle. Oracle considers this only as a "lack of documentation" not as a bug! So, the old Oracle join syntax is still required. – Wernfried Domscheit Jan 01 '16 at 09:26
3

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.

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
Paul
  • 31
  • 1
3

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

pahariayogi
  • 1,073
  • 1
  • 7
  • 18
-1

USE THIS CODE

CREATE MATERIALIZED VIEW MV_ptbl_Category2 BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS SELECT * FROM ptbl_Category2;

Note- MV_ptbl_Category2 is the Materialized view name

Ptbl is the table name.

Matthew Iwundu
  • 1
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 25 '22 at 01:46