Materialized View Refresh ON COMMIT

A quick demonstration of a Materialized View that is refreshed ON COMMIT.

NOTE : This blog post has been updated after Yuri pointed out an error.

SQL> create table source_table
  2  as select rownum as source_pk, dbms_random.string('X',15) as col_2, sysdate-1000+rownum as col_3
  3  from dual connect by level < 1001
  4  /

Table created.

SQL> 
SQL> alter table source_table add constraint source_pk primary key (source_pk);

Table altered.

SQL> 
SQL> create materialized view log on source_table ;

Materialized view log created.

SQL> 
SQL> create materialized view my_mv refresh on commit
  2  as select  source_pk as key_col, col_3 as data_col from source_table
  3  /

Materialized view created.

SQL> 
SQL> select count(*) from source_table;

  COUNT(*)
----------
      1000

SQL> select count(*) from my_mv;

  COUNT(*)
----------
      1000

SQL> 
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> insert into source_table values (5001,'XXXXX',sysdate+50);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from my_mv where key_col=5001;

   KEY_COL DATA_COL
---------- ---------
      5001 03-OCT-12

SQL> 

.
The trace file shows, among other SQLs :

INSERT /*+ IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABLE" (dmltype$$,old_new$$,
  snaptime$$,change_vector$$,xid$$,"SOURCE_PK") 
VALUES
 (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1          2         29           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          1          2         29           1

DELETE FROM "HEMANT"."MY_MV" SNAP$
WHERE
 "KEY_COL" IN (SELECT DISTINCT LOG$."KEY_COL" FROM (SELECT MLOG$."SOURCE_PK"
  "KEY_COL" FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1
  AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."KEY_COL") NOT IN (SELECT
  MAS_TAB$."SOURCE_PK" "KEY_COL" FROM "SOURCE_TABLE" "MAS_TAB$" WHERE
  LOG$."KEY_COL" = MAS_TAB$."SOURCE_PK"))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0         10          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0         10          0           0

MERGE INTO "HEMANT"."MY_MV" "SNA$" USING (SELECT CURRENT$."KEY_COL",
  CURRENT$."DATA_COL" FROM (SELECT "SOURCE_TABLE"."SOURCE_PK" "KEY_COL",
  "SOURCE_TABLE"."COL_3" "DATA_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE")
  CURRENT$, (SELECT DISTINCT MLOG$."SOURCE_PK" "KEY_COL" FROM
  "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1      AND
  ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."KEY_COL" = LOG$."KEY_COL")"AV$"
  ON ("SNA$"."KEY_COL" = "AV$"."KEY_COL") WHEN MATCHED THEN UPDATE  SET
  "SNA$"."KEY_COL" = "AV$"."KEY_COL","SNA$"."DATA_COL" = "AV$"."DATA_COL"
  WHEN NOT MATCHED THEN INSERT  (SNA$."KEY_COL",SNA$."DATA_COL") VALUES
  (AV$."KEY_COL",AV$."DATA_COL")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1         33         26           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1         33         26           1

delete from "HEMANT"."MLOG$_SOURCE_TABLE"
where
 xid$$ = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7          2           1

.