Complete Refresh of Refresh group [message #439856] |
Tue, 19 January 2010 14:11 |
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 |
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 |
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 |
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 |
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
|
|
|