1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Mv refresh on commit

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Jun 7, 2019.

Tags:
  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi

    We have one materialized view (ABC) which was configured to be refreshed on DEMAND.This ABC will be refreshed at the end of the day by using one procedure.There is one more count MV (ABC_COUNT) which configured to be refresh ON COMMIT;


    When the refresh was happening,first its deleting the total data from MV and re populating the fresh data.


    There are some others jobs are executing the at the same time which depends on the same MV few times it leads to missing of some records.

    In order to resolve this issue I want to depend on the ABC_COUNT mv to get the exact count.

    Here my question was what will be the result of the ABC_COUNT mv when the actual ABC mv refresh was happening?

    Is there any change to get the ZERO count just like ZERO results in MV ?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,685
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It will be 0 until a commit is issued at the end of the refresh.
     
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thanks for your interest to resolve the issues.


    Count MV is calculating the counts from the one more MV.

    ABC MV - Refresh on Demand
    ABC_COUNT_MV REfresh on Commit;
    In this case when ABC_MV refresh happening there are no commits to the ABC_MV .
    Even though also it will contains the ZERO Results

    Thanks
    Revathi.T
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,685
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    When the refresh is occurring no session but the one executing the refresh can 'see' the inserted records. The count WILL be 0 during the refresh process, which has been explained already. A 0 count is only a problem AFTER the refresh completes; no rows in the MV indicates a problem with the MV and it probably will need to be rebuilt.