Forum Stats

  • 3,855,355 Users
  • 2,264,499 Discussions
  • 7,905,979 Comments

Discussions

Why my mview is not refreshing after commit?

2»

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    @jackk,

    I've added a correction to my earlier comment about max() and MVs. It can, of course, work "easily" for single tables if you have the appropriate count() in place because (e.g.) if you delete a row that currently hold the max(thing) and count(thing) was 1 then Oracle can determine that it needs to find a new max().

    The problem comes with the combination of tables and max()'s, and @mathguy has probably highlighted all the important points on that topic.


    On a different tack - I set up your tables with @Solomon Yakobson data set (and also tested a couple of modification of both the tables and the test inserts) to check on an old "fast refresh" problem, viz: how much work it does and how long it takes.

    In my case a single row insert/commit, testing JUST the commit, took about 0.6 seconds, largely thanks to one sys-recursive SQL statement involving a UNION ALL view that wasn't accepting a pushed predicate.

    There are a couple of oddities about your data model, but I think I understand what it's doing - I think you've got two cases of "each X has many versions, but version Y is the 'preferred version' at this moment", hence the foreign keys heading in two directions. But this has resulted in the MV being defined through a chain of joins that isn't following a mandatory PK/FK sequence - and that MIGHT be something blocks any chance of the MV being generally fast refreshable.

    Another thought that crossed my mind was that with the various "on delete cascade" FK constraints, you might find that a single row delete results in a massive thrash through the data as Oracle tries to sort out all the single row modification in sequence that have to take place on the materialized view (even in the first stage of the two stage "join view" -> "aggregate view" that SY demonstrated).

    There is an error in the declarations, by the way: you've got one FK declared as "on delete set null", but the column that has to be set null is declared as NOT NULL.

    Regards

    Jonathan Lewis

    JackK
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond

    @mathguy: MV's with max or min in the SELECT list can be fast refreshable after update/delete if the MV does not have a WHERE clause. 

    Just to clarify - this doesn't apply to MV against single table:

    DROP MATERIALIZED VIEW MV1;
    DROP TABLE EMP1 PURGE;
    CREATE TABLE EMP1 AS SELECT * FROM EMP;
    CREATE MATERIALIZED VIEW LOG ON EMP1 WITH ROWID,SEQUENCE(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) INCLUDING NEW VALUES;
    CREATE MATERIALIZED VIEW MV1
      REFRESH FAST
      ON COMMIT
      AS SELECT E.DEPTNO,
                MIN(E.SAL) MIN_SAL,
                MAX(E.SAL) MAX_SAL,
                COUNT(*) CNT
          FROM  EMP1 E
          WHERE JOB != ENAME
            AND SAL > 0
          GROUP BY E.DEPTNO;
    

    Now:

    SQL> SELECT * FROM MV1;
    
        DEPTNO    MIN_SAL    MAX_SAL        CNT
    ---------- ---------- ---------- ----------
            30        950       2850          6
            20        800       3000          5
            10       1300       5000          3
    
    SQL> UPDATE EMP1 SET SAL = 4999 WHERE ENAME = 'KING';
    
    1 row updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT * FROM MV1;
    
        DEPTNO    MIN_SAL    MAX_SAL        CNT
    ---------- ---------- ---------- ----------
            30        950       2850          6
            20        800       3000          5
            10       1300       4999          3
    
    SQL> DELETE EMP1 WHERE ENAME = 'KING';
    
    1 row deleted.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT * FROM MV1;
    
        DEPTNO    MIN_SAL    MAX_SAL        CNT
    ---------- ---------- ---------- ----------
            30        950       2850          6
            20        800       3000          5
            10       1300       2450          2
    
    SQL> INSERT
      2    INTO EMP1(ENAME,JOB,SAL,DEPTNO)
      3    VALUES('KING','PRESIDENT',9999,10);
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT * FROM MV1;
    
        DEPTNO    MIN_SAL    MAX_SAL        CNT
    ---------- ---------- ---------- ----------
            30        950       2850          6
            20        800       3000          5
            10       1300       9999          3
    
    SQL>
    

    SY.