Home » SQL & PL/SQL » SQL & PL/SQL » Automatic Refresh of Materialize View (Oracle, 9.2.0.6.0, Windows XP)
Automatic Refresh of Materialize View [message #299271] Mon, 11 February 2008 02:47 Go to next message
wasifhassan
Messages: 3
Registered: February 2008
Junior Member
Hi guys,

I have a couple of question regarding the REFRESH option of Materialize Views. I'm just creating Materialized Views for performance optimization.

1) How can I set the Refresh of the Materialize View to 1st day of the month. I think I can put a 30 days gap starting from creation date of the view like ' REFRESH FORCE START WITH SYSDATE NEXT (SYSDATE+30)'; but if the view is not created on 1st, then the next refresh will not be on the 1st of next month. Another thing is if I create the view on 1st of a month that has 31 days, then I guess the first refresh would be on the 31st of the month and I will not get the updated picture on the 1st of next month.
2) Which option of Refresh do you prefer, automatic or manual?


Thanks in advance
Re: Automatic Refresh of Materialize View [message #299272 is a reply to message #299271] Mon, 11 February 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) add_months
2) It depends on the need

Regards
Michel
Re: Automatic Refresh of Materialize View [message #299275 is a reply to message #299272] Mon, 11 February 2008 03:07 Go to previous messageGo to next message
wasifhassan
Messages: 3
Registered: February 2008
Junior Member
Suppose I created the view today i.e., 11-feb-2008 then
First Refresh = 1-Mar-2008
Second Refresh = 1-Apr-2008
carrying on to 1-Dec-2008 and 1-Jan-2009 and so on

Are you saying something like this

REFRESH FORCE START WITH SYSDATE NEXT (to_date('01-' || to_char(add_months(SYSDATE,1),'mon-yyyy'),'dd-mon-yyyy'))

Kindly correct me if I'm wrong.
Re: Automatic Refresh of Materialize View [message #299278 is a reply to message #299275] Mon, 11 February 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trunc(mydate,'month') gives the first day of mydate month.

Regards
Michel
Re: Automatic Refresh of Materialize View [message #299282 is a reply to message #299278] Mon, 11 February 2008 03:26 Go to previous message
wasifhassan
Messages: 3
Registered: February 2008
Junior Member
I forgot that Sad

I will use the following

REFRESH FORCE START WITH SYSDATE NEXT (trunc(add_months(sysdate,1),'month'))

Thanks for helping me out so quickly Smile
Previous Topic: Explain plan
Next Topic: How to aggregate similar rows into one
Goto Forum:
  


Current Time: Sun Dec 04 06:55:15 CST 2016

Total time taken to generate the page: 0.11947 seconds