Suggestions required On MV Implementation !!!!!!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Suggestions required On MV Implementation !!!!!!!!

  1. #1
    Join Date
    Feb 2001
    Posts
    290
    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
    NOLOGGING
    BUILD IMMEDIATE
    REFRESH COMPLETE ON DEMAND
    DISABLE QUERY REWRITE
    AS SELECT DISTINCT
    '1' AS CLIP,
    LOB.LOB_ID,
    LOB.LOB_CD,
    GENRE.GENRE_ID,
    GENRE.GENRE_DESC,
    GENRE.GENRE_DESC AS INSTANCENAME
    FROM
    GENRE,
    GENRE_LOB_XREF,
    LOB,
    GENRE_PRODUCT_XREF
    WHERE
    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'
    ORDER BY
    LOB_CD,
    GENRE_DESC
    ;


    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
    Madhu Reddy
    xdollor@yahoo.com

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    PANDO,

    DB: 8.1.7.2

    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


    Madhu Reddy
    xdollor@yahoo.com

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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
    Gert

  5. #5
    Join Date
    Apr 2002
    Location
    Chennai
    Posts
    27
    Hello Madhu,

    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.

    Srinivas.

  6. #6
    Join Date
    Feb 2001
    Posts
    290
    Thank you Gert , Srinivas...

    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,
    Madhu Reddy
    xdollor@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width