Re: Refresh multiple MV's in a single transaction

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 5 Nov 2008 17:13:40 -0800
Message-ID: <bf46380811051713g34a53138n7884ab1dff3724e4@mail.gmail.com>

On Wed, Nov 5, 2008 at 1:32 PM, Finn Jorgensen <finn.oracledba_at_gmail.com>wrote:

> That's what I thought it was for too, but it's not. See my original post :) >

How is it you know that the refresh is not taking place in a single transaction?

My reason for asking is that I just created a test that shows that complete refresh
MVs in a refresh group are definitely refreshed as part of a single transaction.

Here's an excerpt from the trace file:

2442: XCTEND rlbk=0, rd_only=0
...
5743: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "JS001292"."MVTEST2_MV"("OWNER","TABLE_NAME" ... ...
7283: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "JS001292"."MVTEST_MV"("OWN...
...
7721:XCTEND rlbk=0, rd_only=0

There were no commits between the insert statements.

Here's the test script if you want to try it.

This was on 10.2.0.3

---

drop materialized view mvtest_mv;
drop materialized view mvtest2_mv;
drop table mvtest_mv;
drop table mvtest2_mv;
drop table mvtest;
drop table mvtest2;

set echo on

create table mvtest
as
select owner, table_name, tablespace_name from all_tables
where rownum <= 10000
/

create table mvtest2
as
select *
from mvtest
/

alter table mvtest add constraint mvtest_pk primary key(owner, table_name)
/

alter table mvtest2 add constraint mvtest2_pk primary key(owner, table_name)
/

create materialized view log on mvtest;
create materialized view log on mvtest2;

create table mvtest_mv
as
select *
from mvtest
/

create table mvtest2_mv
as
select *
from mvtest2
/

create materialized view mvtest_mv
on prebuilt table
refresh complete on demand
as
select *
from mvtest
/

create materialized view mvtest2_mv
on prebuilt table
refresh complete on demand
as
select *
from mvtest2
/

begin

        begin
                dbms_refresh.subtract('MVTEST_MV','MVTEST_MV');
        exception
        when others then
                null;
        end;

        begin
                dbms_refresh.subtract('MVTEST2_MV','MVTEST2_MV');
        exception
        when others then
                null;
        end;

end;
/

exec dbms_refresh.destroy('MVTEST_GRP')

begin

        dbms_refresh.make(
                name => 'MVTEST_GRP',
                list =>'MVTEST_MV,MVTEST2_MV',
                next_date => sysdate + 10000, -- as good as never
                interval => 'sysdate + 10000'
        );
        commit;

end;
/

update mvtest set owner = reverse(owner) where rownum <= 100;

update mvtest2 set owner = reverse(owner) where rownum <= 100;

commit;

@@10046

begin

        dbms_refresh.refresh('MVTEST_GRP'); end;
/

@@10046_off

set echo off

---

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

Received on Wed Nov 05 2008 - 19:13:40 CST