Re: Anybody studied refresh mechanism of refresh group?

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: oracle-l L <oracle-l@xxxxxxxxxxxxx>, amit bansal <amit.bansal82@xxxxxxxxx>
  • Date: Mon, 10 May 2010 09:16:11 -0700 (PDT)

Amit,

There's one difference between our tests. You have MV log, but my simple 
test does not:

On remote DB:

create table rmtt1 (x int primary key);
create table rmtt2 (x int primary key);

On local DB:

create materialized view mv1 as select * from yhuang.rmtt1@rmtdb;
create materialized view mv2 as select * from yhuang.rmtt2@rmtdb;

exec dbms_refresh.make('refgroup', 'mv1', sysdate, null, null)
exec dbms_refresh.add('refgroup', 'mv2')
exec dbms_refresh.refresh('refgroup')

So the question is: if I don't have materialized view log, there's no 
snaptime or snaptime$$ column in any new object. How does Oracle make 
sure the last refreshed mview in a refresh group only sees the data as 
of the time the first mview started to be refreshed? A refresh group 
does not require its mviews to have mview logs.

For what it's worth, I traced the simplest case with mview log without 
refresh group without two DBs and the snaptime and snaptime$$ don't 
quite look like what you see. Not sure why. 
(Mine is at the bottom of
http://yong321.freeshell.org/oranotes/RefreshGroup.txt )

Yong Huang

--- On Mon, 5/10/10, amit bansal <amit.bansal82@xxxxxxxxx> wrote:

...
Sql should be similar to below (depending on table/log table name)

SELECT /*+ */ 
"A2"."CO_ID","A2"."TMCODE","A2"."SPCODE","A2"."SNCODE","A2"."CS_SEQNO",
...
FROM  "CONTR_SERVICES" "A2", 
 (SELECT DISTINCT "A3"."CO_ID" "CO_ID","A3"."SNCODE" "SNCODE","A3"."CS_SEQNO" 
"CS_SEQNO" 
 FROM  "SYSADM"."MLOG$_CONTR_SERVICES" "A3" 
 WHERE  "A3"."SNAPTIME$$">:1 
...

Source - 
http://sandeepredkar.blogspot.com/2008/09/materialized-view-fast-refresh-and.html

In above query you will see table name CONTR_SERVICES. 
MLOG$_CONTR_SERVICES is the mview log table. You can see snaptime$$ 
(time) and DMLTYPE$$ indicating that <> D i.e inserts and updates. 
(These are columns in mlog$_<table_name>)

Meanwhile I am not sure why you are not able to find this query in 
source database.

Cheers
Amit



      
--
//www.freelists.org/webpage/oracle-l


Other related posts: