Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Red face Unanswered: Problem with Materialized Views (inkl. Example)

    Hello,
    I can't find the error:

    Normal Table with View Log, and Mat-View with Fast Refresh
    Code:
    CREATE TABLE TABLE1(
      ID INTEGER NOT NULL,
      ART_NR INTEGER,
      NAME VARCHAR2(4000),
      ANZAHL INTEGER DEFAULT 0,
      ANGELEGT TIMESTAMP DEFAULT localtimestamp
    , CONSTRAINT TABLE1_PK PRIMARY KEY
      (ID)
    );
    CREATE INDEX TABLE1_INDEX ON TABLE1 (ID, ANGELEGT) NOPARALLEL;
    
    CREATE MATERIALIZED VIEW LOG ON TABLE1
    WITH ROWID (ART_NR,NAME,ANZAHL,ANGELEGT)
    INCLUDING NEW VALUES;
    
    CREATE MATERIALIZED VIEW SUMMEN14TAGE
    NOCACHE NOPARALLEL BUILD IMMEDIATE
    USING INDEX PCTFREE 0 
    REFRESH ON COMMIT FAST USING ENFORCED CONSTRAINTS 
    DISABLE QUERY REWRITE  AS SELECT 
        ART_NR, 
        SUM(ANZAHL) AS SUMANZAHL 
    FROM 
        TABLE1 
    WHERE 
        ANGELEGT > TO_TIMESTAMP('01.05.2008', 'dd.mm.yyyy') 
    GROUP BY 
        ART_NR;
    CREATE INDEX SUMMEN14TAGE_INDEX1 ON SUMMEN14TAGE (ART_NR) NOPARALLEL;
    everthings fine

    Now some data:
    Code:
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('1', '5', 'Schraube', '3');
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('2', '3', 'Mutter', '2');
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('3', '5', 'Schraube', '2');
    commit;
    works fine, Mat-View looks like this:
    Code:
    select * from SUMMEN14TAGE;
    
    ART_NR                 SUMANZAHL              
    ---------------------- ---------------------- 
    5                      5                      
    3                      2                      
    
    2 rows selected
    Now this:
    Code:
    update TABLE1 set Anzahl=4 where ID=3;
    commit;
    no error.

    but the Mat-View is still the same :
    Code:
    select * from SUMMEN14TAGE;
    
    ART_NR                 SUMANZAHL              
    ---------------------- ---------------------- 
    5                      5                      
    3                      2                      
    
    2 rows selected
    in MLOG$_TABLE1 no record.

    Now this:
    Code:
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('4', '9', 'Draht', '10');
    commit;
    Mat-View still:
    Code:
    select * from SUMMEN14TAGE;
    
    ART_NR                 SUMANZAHL              
    ---------------------- ---------------------- 
    5                      5                      
    3                      2                      
    
    2 rows selected
    but in MLOG$_TABLE1:
    Code:
    ART_NR                 NAME         ANZAHL                 ANGELEGT                    M_ROW$$               SNAPTIME$$   DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$  
    ---------------------- ------------ ---------------------- --------------------------- --------------------- ------------ --------- --------- -----------------
    9                      Draht        10                     15.05.08 11:36:40,953000000 AAAHDXAAEAAAkn/AAD    01.01.00     I         N         (RAW)
    
    1 rows selected
    Wat is the Problem?
    Can't get it.

    Many Thanks from Germany
    Tim

  2. #2
    Join Date
    Aug 2007
    Posts
    4
    forgot version:

    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

  3. #3
    Join Date
    Aug 2007
    Posts
    4
    If I start direkt after
    Code:
    update TABLE1 set Anzahl=4 where ID=3;
    commit;
    a manual refresh:
    Code:
    alter materialized view SUMMEN14TAGE consider fresh
    I get this Errormessage:

    ORA-30375 Materialized View kann nicht als abgeglichen betrachtet werden
    materialized view cannot be considered fresh
    *Cause If the materialized view is invalid or unusable, it cannot be considered

    fresh with the ALTER MATERIALIZED VIEW <mv> CONSIDERED FRESH

    comand

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Do you know what all of these do?
    Code:
    NOCACHE NOPARALLEL BUILD IMMEDIATE
    USING INDEX PCTFREE 0 
    REFRESH ON COMMIT FAST USING ENFORCED CONSTRAINTS 
    DISABLE QUERY REWRITE
    Because, perhaps you should first make it work, then make it fancy.
    Code:
    SQL> DROP MATERIALIZED VIEW summen14tage;
    
    Materialized view dropped.
    
    SQL> CREATE MATERIALIZED VIEW SUMMEN14TAGE
      2  REFRESH ON COMMIT
      3  AS SELECT
      4    ART_NR, SUM(ANZAHL) AS SUMANZAHL
      5  FROM
      6    TABLE1
      7  WHERE ANGELEGT > TO_TIMESTAMP('01.05.2008', 'dd.mm.yyyy')
      8  GROUP BY ART_NR;
    
    Materialized view created.
    Code:
    SQL> TRUNCATE TABLE table1;
    
    Table truncated.
    
    
    SQL> INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('1', '5', 'Schraube', '3');
    
    1 row created.
    
    SQL> INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('2', '3', 'Mutter', '2');
    
    1 row created.
    
    SQL> INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('3', '5', 'Schraube', '2');
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT art_nr, anzahl FROM table1;
    
        ART_NR     ANZAHL
    ---------- ----------
             5          3
             3          2
             5          2
    
    SQL> SELECT * FROM summen14tage;
    
        ART_NR  SUMANZAHL
    ---------- ----------
             5          5
             3          2
    Code:
    SQL> UPDATE table1 SET anzahl = 4 WHERE id = 3;
    
    1 row updated.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT * FROM summen14tage;
    
        ART_NR  SUMANZAHL
    ---------- ----------
             5          7
             3          2
    
    SQL>

  5. #5
    Join Date
    Aug 2007
    Posts
    4
    Thank you very much now I try this:

    Code:
    drop materialized view SUMMEN14TAGE;
    
    CREATE MATERIALIZED VIEW SUMMEN14TAGE
    REFRESH ON COMMIT FAST AS SELECT 
        ART_NR, 
        SUM(ANZAHL) AS SUMANZAHL 
    FROM 
        TABLE1 
    WHERE 
        ANGELEGT > TO_TIMESTAMP('01.05.2008', 'dd.mm.yyyy') 
    GROUP BY 
        ART_NR;
        
    TRUNCATE TABLE table1;
    
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('1', '5', 'Schraube', '3');
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('2', '3', 'Mutter', '2');
    INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('3', '5', 'Schraube', '2');
    
    commit;
    output:
    Code:
    drop materialized view succeeded.
    CREATE MATERIALIZED succeeded.
    TRUNCATE TABLE table1 succeeded.
    1 rows inserted
    1 rows inserted
    1 rows inserted
    
    Error starting at line 18 in command:
    commit
    Error report:
    SQL Error: ORA-32321: REFRESH FAST von "REFORMPLUS"."SUMMEN14TAGE" nach TRUNCATE von Detail-Tabelle nicht unterst&#252;tzt
    32321. 00000 -  "REFRESH FAST of \"%s\".\"%s\" unsupported after detail table TRUNCATE"
    *Cause:    A detail table has been truncated and no materialized view
               supports fast refersh after a detail table has been truncated
    *Action:   Use REFRESH COMPLETE.  Note
               materialized view does not support fast refresh after TRUNCATE using
               the DBMS_MVIEW.EXPLAIN_MVIEW() API.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •