INCREMENTAL refresh method for COMPLEX materialized views

Rainer Stenzel

A review of a costum built materialized view implementation raised the idea to integrate such a refresh method into Oracles native materialized views.

It has been achieved by MERGing differences only into the target table (MV) even for complex/non fast refreshable MVs as depicted in the example below.

This might be useful for non fast refreshable MVs that do change rather rarely but should reflect changes quite promptly even so without generating large amounts of redo.

As this MERGE can simply be generated from the MV query and optionally a PK definition this could be done by DBMS_MVIEW.REFRESH with a new method=>incremental.

Example

create table mv_dba_segments as

select owner,tablespace_name,sum(bytes) bytes from dba_segments where tablespace_name not in ('UNDOTBS1','TEMP') group by owner,tablespace_name

merge into mv_dba_segments dest using (

  -->> optimization for PK based UPDATE (instead of DELETE/INSERT)

  select max(MV_ROWID) MV_ROWID,owner,tablespace_name,max(DECODE(MV_ROWID,NULL,bytes,NULL)) bytes, count(1) cnt from (        -- remove for omitting PK based UPDATE

    --> common query for result set differences

    select /*+CARDINALITY (1) */ max(MV_ROWID) MV_ROWID,owner,tablespace_name,bytes, 1 cnt from (

      select rowid MV_ROWID,owner,tablespace_name, bytes from mv_dba_segments

      union

      select NULL MV_ROWID,owner,tablespace_name,sum(bytes) bytes from dba_segments where tablespace_name not in ('UNDOTBS1','TEMP') group by owner,tablespace_name

    ) group by owner,tablespace_name, bytes having count(1)=1

    --<

  )group by owner,tablespace_name                                                                                            -- remove for omitting PK based UPDATE

  --<<

) src

on (src.MV_ROWID = dest.rowid)

when matched then

  update set dest.bytes=src.bytes    -- UPDATE non PK columns

  delete where cnt=1                  -- DELETE old ROWS IN TARGET TABLE

when not matched then

  insert (owner,tablespace_name, bytes)

  values (src.owner,src.tablespace_name, src.bytes)

A lot of interesting stuff around a similar implementation by Stewart Ashton can be found here

Uses cases originating this idea are slow but steadily changing MVs used for OLTP query optimization (by query rewrite or used directly).

The basic intention of refreshing such MVs incrementally is to minimize the impact (increasing number of consistent gets) on concurrently running queries on them by avoiding block changes/redo.

Usually these MVs have multiple indexes to support the OLTP query requirements that generate huge amounts of block changes/redo in the DELETE phase of an atomic COMPLETE refresh.

Currently these MVs will be refreshed plainly periodically but Continuous Query Notification (CQN) (either Query Result Change Notification (QRCN) or Object Change Notification (OCN)) might be considered

for further optimization.

Another yet not considered alternative might be the Out-of-Place refreshing option.

Complete Refresh of Materialized Views: Atomic, Non-Atomic and Out-of-Place

Stew Ashton

Hello,

Thanks for mentioning my work on the subject. However, please refer to my latest version of the package:

https://stewashton.wordpress.com/2018/02/12/comp_sync-1-a-new-table-compare-sync-package/

I am not sure a "one size fits all" solution would suffice. I am already being asked to add options for parallel processing and / or direct path loading of the inserted rows. Plus, there are other issues if the refresh is across a DBLINK.

Looking forward to other comments...

Best regards,

Stew Ashton

Mike Kutz

This would be like "Real-Time Materialized View" but for Complex SQL statements  Right?

Stew Ashton

This would be like "Real-Time Materialized View" but for Complex SQL statements  Right?

Mike Kutz wrote:This would be like "Real-Time Materialized View" but for Complex SQL statements Right?

Not at all. What Rainer means by a "complex SQL statement" is one that, if materialized, is not eligible for "fast refresh" based on MV logs.

A "Real-Time Materialized View" is a fast refresh MV. The only difference is that, if stale, it can still be used in a SQL statement, because the MV logs are used to bring it up to date just for that one statement.

A full refresh removes all the data from the MV, then inserts all the data from the defining query. No MV logs.

This idea would compare the current MV data with the defining query and apply the changes via MERGE. No MV logs.

Regards,

Stew

FatMartinR

On a slight tangent, how about MVs that refresh when referenced (ie; instead of ON-COMMIT or manually) ?

Because, let's face it, why refresh an MV if it's not being referenced?

Mike Kutz

Mike Kutz wrote:This would be like "Real-Time Materialized View" but for Complex SQL statements Right?

Not at all. What Rainer means by a "complex SQL statement" is one that, if materialized, is not eligible for "fast refresh" based on MV logs.

A "Real-Time Materialized View" is a fast refresh MV. The only difference is that, if stale, it can still be used in a SQL statement, because the MV logs are used to bring it up to date just for that one statement.

A full refresh removes all the data from the MV, then inserts all the data from the defining query. No MV logs.

This idea would compare the current MV data with the defining query and apply the changes via MERGE. No MV logs.

Regards,

Stew

Stew Ashton wrote:Mike Kutz wrote:This would be like "Real-Time Materialized View" but for Complex SQL statements Right?Not at all. What Rainer means by a "complex SQL statement" is one that, if materialized, is not eligible for "fast refresh" based on MV logs.A "Real-Time Materialized View" is a fast refresh MV. The only difference is that, if stale, it can still be used in a SQL statement, because the MV logs are used to bring it up to date just for that one statement.A full refresh removes all the data from the MV, then inserts all the data from the defining query. No MV logs.This idea would compare the current MV data with the defining query and apply the changes via MERGE. No MV logs.Regards,Stew

I was attempting to compare the two at the Business Requirements level, not the Implementation level. 

Let's try this again

• Oracle can currently incrementally refresh a Materialized View at COMMIT time for Simple SQL.  (ie FAST REFRESH)
• Oracle can currently incrementally "refresh" a Materialized View at SELECT time for Simple SQL.  (ie Real-Time Materialized Views)  (technically, it produces the results on-the-fly)
• This idea is about giving Oracle the capability to incrementally refresh a Materialized View at COMMIT time for a Complex SQL.
• FatMartinR's post is about giving Oracle the capability to incrementally "refresh" a Materialized View at SELECT time for a Complex SQL. (again, "produce the results on-the-fly")

Is this a good way to relate the Idea(s) to current capabilities?

MK

ps - I have a point to make.  But, in order for you to understand my insanity, you must first understand how I group things together.

"It's all connected!"  -- Dirk Gently's Holistic Detective Agency (the BBC show)

Stew Ashton

Stew Ashton wrote:Mike Kutz wrote:This would be like "Real-Time Materialized View" but for Complex SQL statements Right?

Not at all. What Rainer means by a "complex SQL statement" is one that, if materialized, is not eligible for "fast refresh" based on MV logs.

A "Real-Time Materialized View" is a fast refresh MV. The only difference is that, if stale, it can still be used in a SQL statement, because the MV logs are used to bring it up to date just for that one statement.

A full refresh removes all the data from the MV, then inserts all the data from the defining query. No MV logs.

This idea would compare the current MV data with the defining query and apply the changes via MERGE. No MV logs.

Regards,

Stew

I was attempting to compare the two at the Business Requirements level, not the Implementation level. 

Let's try this again

• Oracle can currently incrementally refresh a Materialized View at COMMIT time for Simple SQL.  (ie FAST REFRESH)
• Oracle can currently incrementally "refresh" a Materialized View at SELECT time for Simple SQL.  (ie Real-Time Materialized Views)  (technically, it produces the results on-the-fly)
• This idea is about giving Oracle the capability to incrementally refresh a Materialized View at COMMIT time for a Complex SQL.
• FatMartinR's post is about giving Oracle the capability to incrementally "refresh" a Materialized View at SELECT time for a Complex SQL. (again, "produce the results on-the-fly")

Is this a good way to relate the Idea(s) to current capabilities?

MK

ps - I have a point to make.  But, in order for you to understand my insanity, you must first understand how I group things together.

"It's all connected!"  -- Dirk Gently's Holistic Detective Agency (the BBC show)

I disagree, because "incrementally" describes an implementation and not a requirement. The requirement is more like "go faster".

Take out the word "incrementally" and then we can discuss your understanding.

The way to "refresh an MV" at SELECT time is to make it just a view. The original idea stated by Rainer would require executing the view anyway, as a prerequisite for applying changes to the MV.

Regards,

Stew

FatMartinR

I disagree, because "incrementally" describes an implementation and not a requirement. The requirement is more like "go faster".

Take out the word "incrementally" and then we can discuss your understanding.

The way to "refresh an MV" at SELECT time is to make it just a view. The original idea stated by Rainer would require executing the view anyway, as a prerequisite for applying changes to the MV.

Regards,

Stew

> The way to "refresh an MV" at SELECT time is to make it just a view.

Not necessarily.     If the MV is complex, then first time after changes to the underlying data, it will take a while to execute, but next time (with no further changes since the last time), the MV would not need to be refreshed first. 

Stew Ashton

> The way to "refresh an MV" at SELECT time is to make it just a view.

Not necessarily.     If the MV is complex, then first time after changes to the underlying data, it will take a while to execute, but next time (with no further changes since the last time), the MV would not need to be refreshed first. 

FatMartinR wrote:...If the MV is complex, then first time after changes to the underlying data, it will take a while to execute, but next time (with no further changes since the last time), the MV would not need to be refreshed first.

How will Oracle know there were no changes since last time? The original "idea" doesn't say anything about that.

Regards,

Stew

FatMartinR

FatMartinR wrote:...If the MV is complex, then first time after changes to the underlying data, it will take a while to execute, but next time (with no further changes since the last time), the MV would not need to be refreshed first.

How will Oracle know there were no changes since last time? The original "idea" doesn't say anything about that.

Regards,

Stew

Flags on the underlying tables?

Mike Kutz

Flags on the underlying tables?

FatMartinR wrote:Flags on the underlying tables?

"Flags" doesn't make sense.  (maybe Heat Map or other related technology?)

"Materialized View Logs" does.

Oh... Some Domain Indexes will use a table for the same purpose:  keep track of what rows changed and how (Insert, Update, Delete).  sometimes, that table will include the new/old values.

MK