- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
INCREMENTAL refresh method for COMPLEX materialized views
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.
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
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
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)
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.