Skip to Main Content
  • Questions
  • Alternative to Fast Updating Materialized Views?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andrew.

Asked: July 13, 2020 - 2:51 pm UTC

Last updated: July 16, 2020 - 1:04 am UTC

Version: Oracle 19c

Viewed 1000+ times

You Asked

Due to various external constraints, it is not possible to setup fast refreshes on the remote database for some materialized views.
However, these tables have "last modified date" type fields (Triggers that update the column on insert/update). Most of these tables also do not get delete statements during normal use. For those that do have deleted data, there may be audit tables available that could be used to find those records.

Is it possible to have the fast-refresh process tap into these fields instead of whatever mechanism is normally used?

If we were to roll our own "fast refresh" process, how do we keep a table marked as a materialized view but allow it to be updated without having it attempt to push the changes back? Ideally, we could force a normal dbms_mview.refresh at any time (no resetting in-place materialized view) to do a full refresh in the event that the custom fast refresh process has gotten out of sync.

and Connor said...

Probably the closest I think you will get is a "never refresh" mview, eg

SQL> create table t1 as select owner, count(*) x from t group by owner;

Table created.

SQL>
SQL> create materialized view t1
  2  on prebuilt table
  3  never refresh 
  4  as select owner, count(*) x from t group by owner;

Materialized view created.


As the name suggests you can *never* refresh this mview, but you *can* make DML changes to the underlying table,

SQL> delete from t1 where rownum <= 10;

10 rows deleted.



Thus if ever wanted to do a "resync" you would probably need to do a manual truncate/insert-append

But I stress....this seems to be heading down a slippery slope. I'd be wanting some sign off from business reps that they acknowledge that data integrity is now a manual responsbility and not ensured by the database.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Writeable Views are Desupported.

Andrew Bay, July 14, 2020 - 11:35 am UTC

I found what I wanted, sort of. "Writeable" Materialized views. But because they share their syntax with Updatable, it is impossible to know which one it went with. These are also desupported features.
It is looking like I will need to just create the table and maintain it with merge statements and the occasional truncate-insert refresh. When I tried making the writeable views, it tried desperately to make it updateable and threw an error for a couple of the tables. "SQL Error: ORA-12013: updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh
12013. 00000 - "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh""
It would be nice if the create materialized view had "FOR WRITEABLE" as an option that allowed an MView to be updatable for any query with the caveat that it always does a complete refresh replacing your modified data.
Connor McDonald
July 16, 2020 - 1:04 am UTC

Updatable snapshots (which were the precursor) were always troublesome in terms of data integrity and were superceded by goldengate (which I concede is not cheap).

We encourage all people with enhancement ideas to put them here

https://community.oracle.com/community/groundbreakers/database/database-ideas/overview

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.