This discussion is archived
14 Replies Latest reply: Feb 14, 2008 4:32 AM by 291229 RSS

Materialized view Refresh

426588 Newbie
Currently Being Moderated
We created a materialized view by joining 3 tables and scheduled for a "COMPLETE REFRESH" every 4 hours. The materialized view creation took about 3 Minutes. We couldn't go for a fast refresh option as the joining logic was complex and we got oracle error when we tried. We are only left with a complete refresh option.
The data from this view is accessed through a web application. When we try to query for the data exactly after the refresh started, we get no results. This will be an issue because Users will have interruption every time the refresh happens. The requirement is we need to have user get data any time he queries instead of giving zero results. Is there something we can do to minimize user interruption.

Thanks in advance for your suggestions.

Chandu
  • 1. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Chandu

    I guess that you are using dbms_mview.refresh and set the parameter atomic_refresh to false. If it is the case, you should set it to true. If not, please, provide more detail about the method you are using for the refresh.

    HTH
    Chris
  • 2. Re: Materialized view Refresh
    426588 Newbie
    Currently Being Moderated
    Chris,
    If I understand you correctly, if atomic refresh is set to TRUE, materialized view truncate and refresh happens in one transaction, that way users always get some data.

    I defined my materialized view defined as below which is scheduled to be refreshed every 4 hours, I don't have much idea about materialized views, so I am not sure if this uses dbms_mview to refresh.

    CREATE MATERIALIZED VIEW m1UsgViw
    REFRESH COMPLETE
    START WITH SYSDATE
    NEXT SYSDATE+1/6
    ENABLE QUERY REWRITE
    AS
    SELECT
    ............ from tbl1, tbl2, tbl3.

    Thanks
    Chandu
  • 3. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Chandu
    if atomic refresh is set to TRUE, materialized view truncate and
    refresh happens in one transaction
    With atomic refreshes the materialized view is not truncated, it is deleted. That delete is performed in the same transaction as the updating.
    I am not sure if this uses dbms_mview to refresh.
    If you execute the following query you should be able to find out the call used for the refresh.

    SELECT * FROM dba_jobs


    HTH
    Chris
  • 4. Re: Materialized view Refresh
    426588 Newbie
    Currently Being Moderated
    Chris,
    Thanks for fast response. Probably it would take longer to refresh because of delete command instead of truncate command. I can verify that. Below is the output from dba_jobs. I am assuming it is using dbms_refresh command. Is it different from dbms_mview ?. What should I do to force oracle to use dbms_mview.

    Job User Schem Last Date Next Date B Failed Command
    ---- ----- ----- ---------------- ---------------- - ------ --------------------
    61 AVBOM AVBOM 02.07.2008 14:03 02.07.2008 18:03 N 0 dbms_refresh.refresh
    62 AVBOM AVBOM 02.07.2008 14:11 02.07.2008 18:11 N 0 dbms_refresh.refresh
  • 5. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Chandu
    Probably it would take longer to refresh because of delete
    command instead of truncate command.
    This is not only probable, it is sure.
    Below is the output from dba_jobs.
    Mhmm... the call to the procedure refresh should have some parameters...
    am assuming it is using dbms_refresh command. Is it different from dbms_mview ?
    Yes. The package dbms_refresh is designed to refresh a so-called "refresh group", not only a materialized view.
    What should I do to force oracle to use dbms_mview.
    If the refresh options in the CREATE MATERIALIZED VIEW statement creates this job, IMHO, the only way to do it is to:
    - not specify those options
    - schedule a job to do the refresh

    HTH
    Chris
  • 6. Re: Materialized view Refresh
    426588 Newbie
    Currently Being Moderated
    As a test I executed with automatic refresh set to TRUE and FALSE. In one session I was running these scripts and in another session I was doing count (*) . Looks like in both the cases the table was truncated and I didn't find any difference in time for refresh.

    Below are the commands executed.
    With Automatic Refresh TRUE
    exec DBMS_MVIEW.REFRESH('M1USGVIW', 'C', '', TRUE, FALSE, 0,0,0, TRUE);

    With Automatic Refresh FALSE
    exec DBMS_MVIEW.REFRESH('M1USGVIW', 'C', '', TRUE, FALSE, 0,0,0, FALSE);

    I also took the explain plans,
    when ran with automatic refresh FALSE, I saw truncate command.
    Surprisingly when I ran with automatic refresh TRUE, I saw delete command first and truncate command later.

    Am I missing something ?
  • 7. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Mhmm.... Which is the version of the database? (4 digit)
  • 8. Re: Materialized view Refresh
    426588 Newbie
    Currently Being Moderated
    Oracle version is 9.2.0.4
  • 9. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Hi Chandu

    Ok, now I understand... This is a typical example of what can happen when the version in use is not specified!

    Up to Oracle9i Release 2 there is a bug. Actually, the one you are seeing... i.e. truncate instead of delete even if atomic_refresh is set to true. Note that as of Oracle Database 10g it works correctly.

    To workaround that problem you should refresh several materialized view in one call. For example with something like that: dbms_mview.refresh('mv1,mv2')

    HTH
    Chris
  • 10. Re: Materialized view Refresh
    26741 Oracle ACE
    Currently Being Moderated
    If you need to refresh only 1 MV called 'mv1', the other option is to use a dummy
    MV.

    CREATE MATERIALIZED VIEW DUMMY_MV AS SELECT SYSDATE FROM DUAL;

    execute dbms_mview.refresh('MV1','DUMMY_MV');
  • 11. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Yeap. I agree...
  • 12. Re: Materialized view Refresh
    426588 Newbie
    Currently Being Moderated
    Thanks Chris, apologies for not specifiying oracle version. Our materialized view will have about 1 million records. I guess atomic refresh may not even worth considering. I am trying a different option, let me know if this is a good idea or is this sounds stupid or if we are re-engineering too much.
    1. Lets assume that we create dummy table which stores just the materialized view name that user will issue query against.
    2. Before the original materialized view is refreshed, we create a second materialized view (m1_bak) from the first one and also lets assume we update the dummy table with m1_bak.
    3. After the first materialized view is refreshed, then we again update the dummy table with original materialized view name.
    4. Before user requests data, we first read the dummy table and get the table name that we should issue query against and will redirect our query accordingly.

    Not sure if I was able to explain better. In short we make a back up of the original materialized view, and maintain a pointer to the table in seperate table and redirect user queries.
  • 13. Re: Materialized view Refresh
    Chris Antognini Explorer
    Currently Being Moderated
    Chandu
    Our materialized view will have about 1 million records.
    I guess atomic refresh may not even worth considering.
    The decision should not be based on the number of rows. But on the amount of time and resources you have to refresh the MV.
    let me know if this is a good idea or is this sounds
    stupid or if we are re-engineering too much.
    I know a couple of environments where there are multiple copies of the "same" table for similar purposes. All of them simply use synonyms. Simply put, the application references only a synonym. The job in charge for the refresh, before starting it, alters the synonym to reference another table. In this way no special coding is necessary in the application.

    HTH
    Chris
  • 14. Re: Materialized view Refresh
    291229 Newbie
    Currently Being Moderated
    How do you actually access the mview?
    Using Query Rewrite or directly accesing the table?

    If it's query rewrite a better option than synonyms/ and your own metda data table would beALTER MATERIALIZED VIEW ENABLE/DISABLE QUERY REWRITE.