RE: Oracle 9i vs 10g MV refresh behaviour
- From: "Johnson, William L (TEIS)" <WLJohnson@xxxxxxxxxxxxxxxxxxx>
- To: <piontekdd@xxxxxxxxx>, <smishra_97@xxxxxxxxx>
- Date: Wed, 5 Mar 2008 14:36:03 -0500
When you upgrade to Oracle 10g, the behavior changes even if the
Materialized views are not dropped and recreated. This is the kind of
change in behavior that makes our lives interesting. Changing the
default behavior for a MV with 19 million rows doesn't work so well when
you move from truncate to delete...
________________________________
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.
<http://us.rd.yahoo.com/evt=51734/*http:/tools.search.yahoo.com/newsearc
h/category.php?category=shopping>
Other related posts:
