Re: Oracle 9i vs 10g MV refresh behaviour
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: "Freeman, Donald" <dofreeman@xxxxxxxxxxx>, piontekdd@xxxxxxxxx
- Date: Wed, 5 Mar 2008 12:11:37 -0800 (PST)
Thanks for the update. It means the only way is to modify all MV to refresh
with Atomic Refresh. I have a DW where we have several big 200+G MV refreshed
qurterly. Here Delete can be big issue and so trying to test it before planning
the upgrade in Test env.
Sanjay
----- Original Message ----
From: "Freeman, Donald" <dofreeman@xxxxxxxxxxx>
To: piontekdd@xxxxxxxxx; smishra_97@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, March 5, 2008 2:53:14 PM
Subject: RE: Oracle 9i vs 10g MV refresh behaviour
To get them to act like they did in 9i you have to use "Atomic Refresh" in your
refresh options.
Donald Freeman
Database Administrator II
Commonwealth of Pennsylvania
Department of Health
Bureau of Information Technology
2150 Herr Street
Harrisburg, PA 17103
dofreeman@xxxxxxxxxxx
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Bradd Piontek
Sent: Wednesday, March 05, 2008 2:09 PM
To: smishra_97@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle 9i vs 10g MV refresh behaviour
Sanjay,
Yes, this is the default behavior in 10g and to modify it you need to use the
DBMS_MVIEW package (easy enough to script or write an anonymous PL/SQL for). I
personally like the change in behavior as it makes the data more accessible
(always had those users hitting the table while the refresh was occuring and
seeing empty rows).
I'm not sure what happens when you upgrade. I would guess the views would work
as they did in 9i and only work as the default if you changed them or
re-created them, but I could be wrong.
Bradd
On Wed, Mar 5, 2008 at 12:49 PM, Sanjay Mishra <smishra_97@xxxxxxxxx> wrote:
Is it correct that upgrading from 9i to 10g will change the MV refresh
behaviour. I checked in one forum that it will use delete command for complte
refresh instead of Truncate. Has anybody seen this issue. If this is true then
Is there any parameter in init.ora that can change the default behaiour of 9i
like using Truncate. I checked the forum and they are referring Atomic Refresh
attribute in DBMS_mview can revert the process but it is not good to modify
several MV.
Is it only true when we move from 9i to 10g or also true if MV are created in
10g itself.
TIA
Sanjay
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
Other related posts:
