Forum Stats

  • 3,750,458 Users
  • 2,250,181 Discussions
  • 7,866,986 Comments

Discussions

materialized view not working properly

chandra_1986
chandra_1986 Member Posts: 274 Blue Ribbon

HI Experts,


Created below materialized view, but output from main query and materiliazed view are not same.

Create Materialized view BSIM_JOB_COUNT

   refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as


SELECT TIMESTAMP,

 SUM(TOTAL_TFS_COUNT) AS TOTAL_TFS_COUNT

FROM

 (SELECT TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY') AS TIMESTAMP,

  COUNT(UNIQUE A.JOBID)             AS TOTAL_TFS_COUNT

 FROM ATLAS_AGENTACT_PROD A,

  ATLAS_AGENTLOGIN B

 WHERE A.EVENTID       IN ('ATLAS020')

 AND B.AGENTROLE       IN ('BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH','BS_MGR')

 AND TRUNC(A.TIMESTAMP)    = TRUNC(SYSDATE-1)

 AND A.ATTUID         =B.ATTUID

 AND TRUNC(B.LOGINLOGOUTTIME) = TRUNC(SYSDATE-1)

 GROUP BY B.AGENTROLE,

  TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY')

 )

  


GROUP BY TIMESTAMP



output from query:

07/25/21 679



from Materilazed view:


07/24/21 1041


Kindly Suggest.

Answers

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    The data may be stale. Run a refresh then check again.

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    BTW it's better to remove the trunc function from your columns:

    AND A.TIMESTAMP >= TRUNC(SYSDATE-1) AND A.TIMESTAMP < TRUNC(SYSDATE)

    AND B.LOGINLOGOUTTIME >= TRUNC(SYSDATE-1) AND B.LOGINLOGOUTTIME < TRUNC(SYSDATE)

  • chandra_1986
    chandra_1986 Member Posts: 274 Blue Ribbon

    we have created materalized view with refresh on every minute:


    Create Materialized view BSIM_JOB_COUNT

       refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as


    SELECT TIMESTAMP,

     SUM(TOTAL_TFS_COUNT) AS TOTAL_TFS_COUNT

    FROM

     (SELECT TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY') AS TIMESTAMP,

      COUNT(UNIQUE A.JOBID)             AS TOTAL_TFS_COUNT

     FROM ATLAS_AGENTACT_PROD A,

      ATLAS_AGENTLOGIN B

     WHERE A.EVENTID       IN ('ATLAS020')

     AND B.AGENTROLE       IN ('BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH','BS_MGR')

     AND TRUNC(A.TIMESTAMP)    = TRUNC(SYSDATE-1)

     AND A.ATTUID         =B.ATTUID

     AND TRUNC(B.LOGINLOGOUTTIME) = TRUNC(SYSDATE-1)

     GROUP BY B.AGENTROLE,

      TO_CHAR(TRUNC(A.TIMESTAMP), 'MM/DD/YY')

     )

      


    GROUP BY TIMESTAMP



    where we need to run refresh , any suggestions pls.

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    The refresh interval is one minute, if there's any change on the base tables during the minute then they won't match.

    But it looks like you are reading the data from the previous day and usually the result should be static.

    When you compare the numbers, do you see static numbers? Does the result from "main query" keep changing?

    To run a manual refresh you can simply call dbms_mview.refresh procedure.

  • chandra_1986
    chandra_1986 Member Posts: 274 Blue Ribbon

    Yes data from main query is getting updated in one day, so last day date data from base table.

  • chandra_1986
    chandra_1986 Member Posts: 274 Blue Ribbon

    Base query is getting updated in one day, so shall we keep the materialized view with one day refresh interval time rite.

  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    If the data of the base tables gets updated only once a day, then your mview can be scheduled to refresh immediately after the data load and the refresh interval can be set to one day. It's not necessary to refresh every minute.

    But your original question is regarding data mismatch between mview and base tables. Did you figure that out? Is it still hapening?

    You can check LAST_REFRESH_DATE on all_mviews.

    chandra_1986
  • chandra_1986
    chandra_1986 Member Posts: 274 Blue Ribbon

    Thanks James,


    No there is no data mismatch between base table and MVs.

    Wanted to know if refresh interval is defined while creating MVs, then also why MV is not getting refresh here.


    Create Materialized view BSIM_JOB_COUNT

       refresh complete start with (sysdate) next (sysdate+1/1440) with rowid as


    SELECT TIMESTAMP,

     SUM(TOTAL_TFS_COUNT) AS TOTAL_TFS_COUNT

    FROM

  • EdStevens
    EdStevens Member Posts: 28,387 Gold Crown