Re: Refresh multiple MV's in a single transaction
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: finn.oracledba@xxxxxxxxx
- Date: Wed, 5 Nov 2008 17:13:40 -0800
On Wed, Nov 5, 2008 at 1:32 PM, Finn Jorgensen <finn.oracledba@xxxxxxxxx>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
=================================
Other related posts:
