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!

Materialized views: How to check if tables (on which is based MV) were changed?

Discussion in 'General' started by eee, Nov 3, 2011.

  1. eee

    eee Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I've created a MV, on some tables. I don't want to auto-refresh it. I just want to know whether original tables were changed or not. What is the best way to do that?
    I want to have a flag that shows, whether MV is actual or not.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What do you mean by 'changed'? Data inserted/updated/deleted? Master table definition changed? I expect it's the first case but it is better for us if you are clear in your description.

    You can't have a 'flag' showing it's stale but you can query USER_MVIEWS for that information:

    Code (SQL):
    SQL> CREATE materialized VIEW yarpno_mv
      2  build  immediate
      3  refresh fast ON demand
      4  AS SELECT sming, alroo, repnart FROM yarpno;
     
    Materialized VIEW created.
     
    SQL>
    SQL> SELECT sming, alroo, repnart FROM yarpno_mv;
     
         SMING ALROO        REPNART
    ---------- ------------ --------
             1 Smeezo 1     Twe1
             2 Smeezo 2     Twe2
             3 Smeezo 3     Twe3
             4 Smeezo 4     Twe4
             5 Smeezo 5     Twe5
             6 Smeezo 6     Twe6
             7 Smeezo 7     Twe7
             8 Smeezo 8     Twe8
             9 Smeezo 9     Twe9
            10 Smeezo 10    Twe10
            11 Smeezo 11    Twe11
    ...
           498 Smeezo 498   Twe498
           499 Smeezo 499   Twe499
           500 Smeezo 500   Twe500
     
    500 ROWS selected.
     
    SQL>
    SQL> DECLARE
      2          yip NUMBER;
      3  BEGIN
      4          FOR i IN 1..100 loop
      5                  yip := i + 500;
      6                  INSERT INTO yarpno
      7                  VALUES (yip, 'Smeezo '||yip, 'Twe'||i, 'QZ'||yip);
      8          END loop;
      9
     10          commit;
     11
     12  END;
     13  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT owner, mview_name, staleness FROM user_mviews;
     
    OWNER                          MVIEW_NAME                     STALENESS
    ------------------------------ ------------------------------ -------------------
    BING                           YARPNO_MV                      NEEDS_COMPILE
     
    SQL>
     
    Does this information help you?
     
  3. eee

    eee Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I mean update/insert/deleted... I can't do mv with 'refresh fast' option.

    But I think I've already got it.. I use "SELECT STALENESS FROM user_mviews WHERE mview_name = ...", is that good solution?

    again.. I want to know, if MV shows the actual data - and refresh it only on demand (by user), when the flag shows ("tables were updated, please refresh mv").. sth like that..

    btw. refresh like that: exec DBMS_SNAPSHOT.REFRESH( 'TEST_MV','C');
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How are you planning on creating this 'flag'? If the materialized view can be updated a trigger on the materialized view wouldn't be of any use since updating the materialized view doesn't update the base table or tables:

    Code (SQL):
     
    SQL> CREATE materialized VIEW yarpno_mv
      2  build  immediate
      3  refresh fast ON demand
      4  FOR UPDATE
      5  AS SELECT sming, alroo, repnart FROM yarpno;
     
    Materialized VIEW created.
     
    SQL>
    SQL> CREATE OR REPLACE TRIGGER yarpno_mv_trig
      2  after INSERT OR UPDATE OR DELETE ON yarpno_mv
      3  FOR each ROW
      4  DECLARE
      5          v_staleness     user_mviews.staleness%TYPE;
      6  BEGIN
      7          SELECT staleness INTO v_staleness
      8          FROM user_mviews
      9          WHERE mview_name = 'YARPNO_MV';
     10
     11          IF v_staleness = 'NEEDS_COMPILE' THEN
     12                  raise_application_error(-20998, 'Materialized view needs to be refreshed.');
     13          END IF;
     14
     15  END;
     16  /
     
    TRIGGER created.
     
    SQL>
    SQL> UPDATE yarpno_mv
      2  SET sming = sming+600;
     
    500 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT sming, alroo, repnart FROM yarpno_mv;
     
         SMING ALROO        REPNART
    ---------- ------------ --------
           601 Smeezo 1     Twe1
           602 Smeezo 2     Twe2
           603 Smeezo 3     Twe3
           604 Smeezo 4     Twe4
           605 Smeezo 5     Twe5
           606 Smeezo 6     Twe6
           607 Smeezo 7     Twe7
           608 Smeezo 8     Twe8
           609 Smeezo 9     Twe9
           610 Smeezo 10    Twe10
           611 Smeezo 11    Twe11
    ...
          1096 Smeezo 496   Twe496
          1097 Smeezo 497   Twe497
          1098 Smeezo 498   Twe498
          1099 Smeezo 499   Twe499
          1100 Smeezo 500   Twe500
     
    500 ROWS selected.
     
    SQL>
    SQL> SELECT owner, mview_name, staleness FROM user_mviews;
     
    OWNER                          MVIEW_NAME                     STALENESS
    ------------------------------ ------------------------------ -------------------
    BING                           YARPNO_MV                      FRESH
     
    SQL>
     
    Only updating the base tables causes the materialized view to become stale:

    Code (SQL):
     
    SQL> DECLARE
      2          yip NUMBER;
      3  BEGIN
      4          FOR i IN 1..100 loop
      5                  yip := i + 500;
      6                  INSERT INTO yarpno
      7                  VALUES (yip, 'Smeezo '||yip, 'Twe'||i, 'QZ'||yip);
      8          END loop;
      9
     10          commit;
     11
     12  END;
     13  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT owner, mview_name, staleness FROM user_mviews;
     
    OWNER                          MVIEW_NAME                     STALENESS
    ------------------------------ ------------------------------ -------------------
    BING                           YARPNO_MV                      NEEDS_COMPILE
     
    SQL>
     
    Notiice the trigger doesn't raise the coded error on either update.

    I don't see any way you can do this in the database.