Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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.

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.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Writeable Views are Desupported.

More to Explore

Design

Alternative to Fast Updating Materialized Views?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Writeable Views are Desupported.

More to Explore

Design