Search My Oracle Blog

Custom Search

09 July, 2012

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

.

3 comments:

Yuri Pedan said...

But what is the price?


PARSING IN CURSOR #8 len=28 dep=1 uid=57 oct=7 lid=57 tim=1311664757398407 hv=276496615 ad='331719b0'
delete from "SCOTT"."MY_MV"
END OF STMT
PARSE #8:c=1000,e=2090,p=0,cr=7,cu=0,mis=1,r=0,dep=1,og=1,tim=1311664757398404
BINDS #8:
EXEC #8:c=18996,e=17880,p=0,cr=3,cu=3068,mis=0,r=1000,dep=1,og=1,tim=1311664757416734
...
PARSING IN CURSOR #8 len=149 dep=1 uid=57 oct=2 lid=57 tim=1311664757421809 hv=3280196009 ad='34170e14'
INSERT /*+ */ INTO "SCOTT"."MY_MV"("KEY_COL","DATA_COL") SELECT "SOURCE_TABLE"."SOURCE_PK","SOURCE_TABLE"."COL_3" FROM "SOURCE_TABLE" "SOURCE_TABLE"
END OF STMT
PARSE #8:c=5000,e=4230,p=0,cr=17,cu=0,mis=1,r=0,dep=1,og=1,tim=1311664757421802
BINDS #8:
EXEC #8:c=8998,e=9123,p=0,cr=21,cu=2047,mis=0,r=1001,dep=1,og=1,tim=1311664757430976

Hemant K Chitale said...

Yuri,

Thanks. I've deleted my post as it was incorrect.

Hemant K Chitale said...

Yuri,
The updated blog post now appears.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016