Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

INCREMENTAL refresh method for COMPLEX materialized views

Rainer StenzelMar 19 2019 — edited Apr 19 2019

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

Comments

Processing

Post Details

Added on Mar 19 2019
11 comments
4,195 views