I have a set of Materialized views in my DB . we refresh ( COMPLETE) these mvs, couple of times a day. Webserver ( application ) will hit these MVs to show the data on web pages. But the complete Refresh of MVs are consuming much time and , at this point of time , Application is not able to show right data on web pages. This is like a down time. I need some suggestions from you all, in order to minimize or zeroing this down time.
The first thing i can think of is , FAST refresh , but one of my Sr.DBA told me that the MV definition will not allow us for a FAST refresh( Are there any limitations for FAST refresh ???? ). Here is a sample MV Definition :
CREATE MATERIALIZED VIEW GENRELOB
REFRESH COMPLETE ON DEMAND
DISABLE QUERY REWRITE
AS SELECT DISTINCT
'1' AS CLIP,
GENRE.GENRE_DESC AS INSTANCENAME
GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND
GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND
GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND
GENRE.DSPLY_IND = 'Y'
My Goal is to view the FRESH data on web pages all the time , irrespective of MV Refresh. Would anybody suggest me some bright ideas , to have no or less down time ???
thanks in advance
My problem here is I can not eliminate the COMPLTE refresh , due to the existing MV code,which can not be changed due to some reasons. SO what i am looking for is, an alternative method to eliminate the down time ( Web pages are appearing blank at the time of MV refresh ), with the same comlete refresh.
Space is not a constraint for me . I would like to eliminate that damn down time , by having another set of same MVs.
Here i would like to know , how can i implement my idea in a best way ?? Or if you have a bright idea to share with ,I would be very glad.
I request you all Advisors to advise me
Thanks in advance
work around :
instead of using the name of the MV in the application. Make a view that refers to the MV.
Just before refreshing the MV, do a CTAS an make a 'temp MV table' with the contents of the MV. Change the view so it uses the table.
Rebuild the MV. Change the view so you use the MV again. Drop the 'temp MV table '.
Hope this helps
I also have some requirement like this to make the MVs available for a real time system. To manage this down time, I am managing a different set of tables, I call them as TEMP tables (MVs). When ever I do the synchronization or complete refresh, I always do the complete refresh on the so called TEMP tables (MVs). Once this refresh is completed, then I rename the original tables to some intermediate tables (I call them as copy tables) and then rename the newly refreshed temp tables to the main tables and finally rename the intermediate tables (so called COPY tables) to temp tables. So, with this at the end of the process, the complete structure is in place. and the down time will be hardaly a matter of second. I think, hope fully, this will help you.
Thank you Gert , Srinivas...
I have got few doubts which you may clarify... Did you find any invalidations of the packages/stored procs while renaming the TABLES ( Original Tables ). ?? Also Is it possible to rename MV to table and table to MV ????
Or you mean ( in your solution to the problem ) table means MV at all the places... I am a bit confused. Since you are already implemented , would you send me an eaxmple to my MAIL id or paste it here in the forum. I would like to see it in step by step in more meaningful manner, This is just a request to you and not a demand ..
Thanks a bunch again,
Click Here to Expand Forum to Full Width