How to do a Synchronous Refresh with Staging Logs in Oracle 12c

The distinguishing feature of synchronous refresh is that changes to a table and its MVs are loaded and refreshed together, hence the name synchronous refresh.

In a typical data warehouse, data preparation consists of extracting the data from one or more sources, cleansing, and formatting it for consistency,and transforming into the data warehouse schema. The data preparation area is called the staging area and the base tables in a data warehouse are loaded from the tables in the staging area. The synchronous refresh method fits into this model because it allows you to load change data into the staging logs.

The following session shows how to set up the base-tables and mv for sync-refresh, create and populate staging logs and use sync-refresh
operations to refresh the group. The sync-refresh catalog views are included in this note in the QUERY sections.

Briefly it demonstrates the following steps:

In order for tables and MVs to be maintained by synchronous refresh, the objects need to be registered for it.
Tables are registered for synchronous refresh when staging logs are created on them and MVs are registered with the REGISTER_MVIEWS procedure.
Registering the mview for Synchronous Refresh implicitly creates group of related objects called sync refresh group.
Then load change data into the staging logs.
Next is to prepare the staging logs. If errors are detected, they are captured in an exceptions table, USER_SR_STLOG_EXCEPTIONS.
Then prepare refresh. This can potentially be a long-running operation because it prepares and loads the outside tables, if those are used.
Finally do the synchronous refresh.

1. Create master tables and insert data

connect syncref_user/syncref_user

CREATE TABLE time(
    time_key        date primary key,
    month            integer,
    year            integer,
    quarter            integer);

CREATE TABLE store(
    store_key        integer primary key,
    store_number        integer,
    store_name        varchar2(20),
    zipcode                 varchar2(20));

CREATE TABLE fact(
    time_key        date not null references time(time_key),
    store_key        integer not null references store(store_key),
    dollar_sales        number (6,2),
    unit_sales        integer)
  PARTITION by RANGE (time_key) 
      (partition fp1  
      values less than (TO_DATE('31-12-1997', 'DD-MM-YYYY'))
        tablespace users,
       partition fp2  
      values less than (TO_DATE('31-01-1998', 'DD-MM-YYYY'))
        tablespace users,
      partition fp3 
      values less than (TO_DATE('28-02-1998', 'DD-MM-YYYY'))
        tablespace users);

INSERT INTO time VALUES('10-DEC-1997', 199712, 1997, 19974);
INSERT INTO time VALUES('10-JAN-1998', 199801, 1998, 19981);

INSERT INTO store VALUES(1, 1, 'Store 1', '03060');
INSERT INTO store VALUES(2, 2, 'Store 2', '03062');

INSERT INTO fact VALUES('10-DEC-1997', 1, 100, 100);
INSERT INTO fact VALUES('10-DEC-1997', 2, 200, 200);

INSERT INTO fact VALUES('10-JAN-1998', 1, 100, 100);
INSERT INTO fact VALUES('10-JAN-1998', 2, 200, 200);

INSERT INTO fact VALUES('10-FEB-1998', 1, 100, 100);
INSERT INTO fact VALUES('10-FEB-1998', 2, 200, 200);
;

2. Create Staging Logs

CREATE MATERIALIZED VIEW LOG on FACT FOR SYNCHRONOUS REFRESH USING ST_FACT;
CREATE MATERIALIZED VIEW LOG on STORE FOR SYNCHRONOUS REFRESH USING ST_STORE;
CREATE MATERIALIZED VIEW LOG on TIME FOR SYNCHRONOUS REFRESH USING ST_TIME;

3. Create Materialized View

CREATE MATERIALIZED VIEW mv1
  PARTITION BY RANGE (time_key)
  (
    partition mvp1
      values less than (TO_DATE('31-12-1997', 'DD-MM-YYYY'))
      tablespace users,

    partition mvp2
      values less than (TO_DATE('31-01-1998', 'DD-MM-YYYY'))
      tablespace users,

    partition mvp3
      values less than (TO_DATE('28-02-1999', 'DD-MM-YYYY'))
      tablespace users
 )
REFRESH USING TRUSTED CONSTRAINTS
 AS
 SELECT f.store_key, s.store_name, t.year, f.time_key,
        SUM(f.dollar_sales) as dollar_sales, 
        COUNT(f.dollar_sales) as cnt_dsales,
        COUNT(*) as cnt
 FROM fact f, store s, time t
 WHERE s.store_key = f.store_key  AND
       f.time_key = t.time_key
 GROUP BY t.year, f.store_key, s.store_name, f.time_key;

4. register Materialized view

execute dbms_sync_refresh.register_mviews('MV1');

QUERY: Display registered objects in the group

col name format a10
col type format a10
col staging_log_name format a16
select name, type, staging_log_name from user_sr_obj 
where group_id = dbms_sync_refresh.get_group_id('MV1') 
order by type, name;

NAME       TYPE       STAGING_LOG_NAME
---------- ---------- ----------------
MV1        MVIEW
FACT       TABLE      ST_FACT
STORE      TABLE      ST_STORE
TIME       TABLE      ST_TIME

4 rows selected.

5. Insert change-data into Staging Logs

insert into  st_time (dmltype$$, time_key, month, year, quarter)
values ('I', '11-FEB-1998', 199802, 1998, 19982);

insert into st_store (dmltype$$, store_key, store_number, store_name, zipcode)
values ('I',   5, 5, 'Store 5', '03060');

insert into st_store (dmltype$$, store_key, store_number, store_name, zipcode)
values ('I',   6,    6, 'Store 6', '03062');

insert into st_fact (dmltype$$, time_key, store_key, dollar_sales, unit_sales)
values('I', '11-FEB-1998', 1, 100, 100);

insert into st_fact (dmltype$$, time_key, store_key, dollar_sales, unit_sales)
values('I', '11-FEB-1998', 2, 200, 200);

insert into st_fact (dmltype$$, time_key, store_key, dollar_sales, unit_sales)
values('D', '10-FEB-1998', 2, 200, 200);

insert into st_fact (dmltype$$, time_key, store_key, dollar_sales, unit_sales)
values('I', '11-FEB-1998', 5, 500, 500);

6. Prepare Staging Logs

execute dbms_sync_refresh.prepare_staging_log('syncref_user', 'fact');
execute dbms_sync_refresh.prepare_staging_log('syncref_user', 'time');
execute dbms_sync_refresh.prepare_staging_log('syncref_user', 'store');

QUERY: Check status of staging logs after preparation

col table_name format a10
col staging_log_name format a16
col num_inserts format 999
col num_deletes format 999
col num_updates format 999

col operation format a10
col name format a16
col status format a16

col store_key format 9999
col dollar_sales format 99999
col cnt_sales format 99999
col cnt format 999
col time_key format a10
col store_name format a11
select table_name, staging_log_name, num_inserts, num_deletes, num_updates 
from user_sr_stlog_stats
order by table_name;

TABLE_NAME STAGING_LOG_NAME NUM_INSERTS NUM_DELETES NUM_UPDATES
---------- ---------------- ----------- ----------- -----------
FACT       ST_FACT                    4           1           1
STORE      ST_STORE                   2           1           1
TIME       ST_TIME                    1           0           0

3 rows selected.

QUERY: Display base-tables contents before refresh.

select * from time order by time_key;
select * from store order by store_key;
select * from fact partition (fp1) order by time_key, store_key;
select * from fact partition (fp2) order by time_key, store_key;
select * from fact partition (fp3) order by time_key, store_key;

QUERY: Display MV contents before refresh.

select * from mv1 partition (mvp1) order by time_key, store_key;
select * from mv1 partition (mvp2) order by time_key, store_key;
select * from mv1 partition (mvp3) order by time_key, store_key;

7. Prepare Synchronized Refresh

execute dbms_sync_refresh.prepare_refresh(dbms_sync_refresh.get_group_id('MV1'));

QUERY: Display status of objects after prepare_refresh.

select table_name, staging_log_name, num_inserts, num_deletes, num_updates 
from user_sr_stlog_stats
order by table_name;

TABLE_NAME STAGING_LOG_NAME NUM_INSERTS NUM_DELETES NUM_UPDATES
---------- ---------------- ----------- ----------- -----------
FACT       ST_FACT                    4           1           1
STORE      ST_STORE                   2           1           1
TIME       ST_TIME                    1           0           0

3 rows selected.

QUERY: Check status of objects in the group.

select name, type, status from user_sr_obj_status 
where group_id = dbms_sync_refresh.get_group_id('MV1') 
order by type, name;

NAME             TYPE       STATUS
---------------- ---------- ----------------
MV1              MVIEW      NOT PROCESSED
FACT             TABLE      NOT PROCESSED
STORE            TABLE      NOT PROCESSED
TIME             TABLE      NOT PROCESSED

4 rows selected.

QUERY: Check status of the group itself after the prepare_refresh. Note the operation field is set to PREPARE and status is COMPLETE.

select operation, status from user_sr_grp_status
where group_id = dbms_sync_refresh.get_group_id('MV1');

OPERATION  STATUS
---------- ----------------
PREPARE    COMPLETE

1 row selected.

8. Execute Synchronized Refresh.

execute dbms_sync_refresh.execute_refresh(dbms_sync_refresh.get_group_id('MV1'));

QUERY: Display status of objects after execute_refresh. The change data have been processed by refresh and so there are no rows.

select table_name, staging_log_name, num_inserts, num_deletes, num_updates 
from user_sr_stlog_stats
order by table_name;

no rows selected