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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Andrew.

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

Answered by: Connor McDonald - Last updated: July 16, 2020 - 1:04 am UTC

Category: Database Development - Version: Oracle 19c

Viewed 100+ 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 we 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.

and you rated our response

  (1 rating)

Reviews

Writeable Views are Desupported.

July 14, 2020 - 11:35 am UTC

Reviewer: Andrew Bay from United States

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

Followup  

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.