Oracle - How to create a materialized view with FAST REFRESH and JOINS
Solution 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;
Solution 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
Solution 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.
Solution 4
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
Mike Christensen
Founder and Chief Architect of KitchenPC.com, the world's most powerful recipe search engine. The technology behind KitchenPC is open-source, and available on GitHub.
Updated on October 19, 2020Comments
-
Mike Christensen over 3 years
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 over 12 yearsHmm 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 over 12 yearsUggghhhh 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.
-
Wernfried Domscheit over 8 yearsIn 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.
-
Reversed Engineer over 6 yearsIndexed 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 docs.microsoft.com/en-us/sql/relational-databases/views/…