FAQ
Hi All,

I have a mview(Materialized View) that i refresh
ONDEMAND. This Mview takes 15 minutes in refresh as it
involves a complex query and lots of data in master
table. But as soon as the refresh starts old data gets
deleted from the mview and there is no data in mview
untill the refresh is complete. As i m querying the
mview in my scripts i get no data if some request is
sent during that refresh interval. Can we keep this
old data untill new data gets populated? Or is there
anyway that we can reduce this time period for which
there is no data in mview?

Thanks in advance

Manoj Ahuja

Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Manoj Ahuja
INET: manoj27_07_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Arup Nanda at Jun 3, 2003 at 5:10 pm
    Manoj,

    This is a problem with the full refresh of an MV. The refresh initially
    truncates the table and then issues a full refresh. To resolve this problem,
    here are some steps:

    Create the MV as a table first and then build the MV with "ON PREBUILT
    TABLE" clause.
    When created this way the drop snapshot (or MV) command does __not__ drop
    the segment; the table remains.
    Before doing the full refresh, drop the snapshot
    Copy the underlying table to a new name
    Then recreate a snapshot wth ON PREBUILT TABLE clause.
    Do a full refresh.
    While the full refresh is going on, you can refer to the copied table for
    the previous data.

    I did a presentation at IOUG Live this year which is along similar lines -
    "Painless Alter of Master Table in Replication", which you can find on my
    website, www.proligence.com.

    Hope this helps.

    Arup

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, June 03, 2003 9:19 AM
    Hi All,

    I have a mview(Materialized View) that i refresh
    ONDEMAND. This Mview takes 15 minutes in refresh as it
    involves a complex query and lots of data in master
    table. But as soon as the refresh starts old data gets
    deleted from the mview and there is no data in mview
    untill the refresh is complete. As i m querying the
    mview in my scripts i get no data if some request is
    sent during that refresh interval. Can we keep this
    old data untill new data gets populated? Or is there
    anyway that we can reduce this time period for which
    there is no data in mview?

    Thanks in advance

    Manoj Ahuja

    __________________________________
    Do you Yahoo!?
    Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
    http://calendar.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Manoj Ahuja
    INET: manoj27_07_at_yahoo.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 3, '03 at 1:19p
activeJun 3, '03 at 5:10p
posts2
users2
websiteoracle.com

2 users in discussion

Manoj Ahuja: 1 post Arup Nanda: 1 post

People

Translate

site design / logo © 2022 Grokbase