Re: Anybody studied refresh mechanism of refresh group?

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_at_rmtdb; create materialized view mv2 as select * from yhuang.rmtt2_at_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_at_gmail.com> 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       

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