Home » Server Options » Replication » Complete Refresh of Refresh group (Oracle10.2.0.3, AIX5.3)
Complete Refresh of Refresh group [message #439856] Tue, 19 January 2010 14:11 Go to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
Refresh group refreshing issue.
+++++++++++++++++++++++++++++++

Got a Refresh group with multiple materialized views(MV) needs to be refreshed completely every day. The MVs do have million of records, initial load is much faster; but subsequent loading is very slow.

As it's is 10g, I want to set atomic_refresh to false. Is there a way to set atomic refresh to false at refresh group level?

How do you check the current setting of atomic_refresh for the MV? I checked in dba_dict_columns, but couldn't find anything.

Thanks!

Re: Complete Refresh of Refresh group [message #439864 is a reply to message #439856] Tue, 19 January 2010 14:50 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
Got a Refresh group with multiple materialized views(MV) needs to be refreshed completely every day. The MVs do have million of records, initial load is much faster; but subsequent loading is very slow.


If you have millions of records then why your doing complete refresh? You should select incremental refresh.

DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true,
   nested                 IN     BOOLEAN        := false);


Quote:
As it's is 10g, I want to set atomic_refresh to false. Is there a way to set atomic refresh to false at refresh group level?

How do you check the current setting of atomic_refresh for the MV? I checked in dba_dict_columns, but couldn't find anything


By default; Automatic_refresh it's true; During refresh you can set false

- Babu

[Updated on: Tue, 19 January 2010 14:56]

Report message to a moderator

Re: Complete Refresh of Refresh group [message #439889 is a reply to message #439856] Tue, 19 January 2010 21:48 Go to previous messageGo to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
Hi Babu,

I understand the functionality of atomic refresh at MV level.
But what I'm looking for is at refresh group level.


I can't do incremental in this case, but complete refresh as I can't add any objects( MV logs in this case) in source DB. First loading takes 12 minutes which is ok with me, but reloading take 35 minutes. I want to make sure it does the truncate rather than delete and refresh group has almost 25 MVs attached to it.


Also how do I check if the atomic_refresh is already set to false for a MV?


Thanks!
Re: Complete Refresh of Refresh group [message #440007 is a reply to message #439889] Wed, 20 January 2010 09:44 Go to previous messageGo to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
Or basically is there a way to set atomic_refresh to false for the materialized view without actually doing the refresh?

Because in this case, refreshing of MV would be done as part of the refresh group schedule.
Re: Complete Refresh of Refresh group [message #440032 is a reply to message #440007] Wed, 20 January 2010 13:25 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
Also how do I check if the atomic_refresh is already set to false for a MV?


You can not check in automatic refresh details in materialized view.

Auotmatic Refresh is parameter in DBMS_MVIEW package. If you want manual refresh then you can configure AUTOMATIC_REFRESH = FALSE.

Quote:
Because in this case, refreshing of MV would be done as part of the refresh group schedule.

>>But what I'm looking for is at refresh group level.


As per as I know. In materialized view group you can not check automatic refresh details.

Quote:
I can't do incremental in this case, but complete refresh as I can't add any objects( MV logs in this case) in source DB. First loading takes 12 minutes which is ok with me, but reloading take 35 minutes. I want to make sure it does the truncate rather than delete and refresh group has almost 25 MVs attached to it


Have you enabled QUERY_REWRITE_* parameter?

- Babu

[Updated on: Wed, 20 January 2010 13:26]

Report message to a moderator

Previous Topic: Materiliezed View Replication Problem
Next Topic: Daily refresh at particular hour/minute
Goto Forum:
  


Current Time: Sat Nov 09 16:38:53 CST 2024