How do I ensure that an Oracle Materialized View is created as a "Writeable" materialized view and not as an "Updatable" one? I never want these views to push data back to the source and I want a complete refresh to overwrite anything I change.
The source problem is that one of the remote databases cannot support materialized view logs, but the tables have last modified timestamp columns. So I'd like to attempt to keep them up to date with Merge statements filtering on that column and then occasionally do normal replication refreshes if any issues turn up.
I tried CREATE MATERIALIZED VIEW Test.SomeTable_MVW TABLESPACE SOME_DATA BUILD DEFERRED REFRESH COMPLETE FOR UPDATE AS (SELECT ... FROM SomeTable@OverThere);
But I got the error:
12013. 00000 - "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh"
*Cause: The updatable materialized view query contained a JOIN, SUBQUERY,
UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master
table did not have a materialized view log.
*Action: Make the materialized view simpler. If a join is really needed,
make multiple simple materialized views then put a view on top of
them. Make sure a materialized view log is created for the master
table.
There is only one remote table. I do not understand why this error even contains or the master table did not have a materialized view log
since that's how you get a writeable materialized view. Too bad there isn't a "FOR WRITEABLE" clause for when you know that you don't want to push data back. And the REFRESH COMPLETE should clue it in that I don't want to push the data back.
Best Answer
Updatable materialized view was an Advanced Replication feature. A writable materialized view was simply an updatable materialized view that was not a member of a materialized view (replication) group.
Desupport of Advanced Replication
You are looking for a solution for a feature that became deprecated 7 years ago (in 12.1) and desupported 4 years ago (12.2).
The
FOR UPDATE
clause still exists for backward compatibility, but it is not even documented anymore.I do not know why Oracle implemented it this way, but at this point, I would just let it go.
For the use case you described, a simple table is fine. You can do incremental refreshes with MERGE on key + timestamp, and do a complete refresh with
truncate
+insert ... select ...
.