Problem mit Materialized Views (Beispiel enthalten)

[chevyvan2000]

Hallo,
ich find den Fehler nicht, also folgendes:

Einfacher Table dann ein View Log, dann der Mat-View mit Fast Refresh

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;

läuft ohne probleme durch

nun ein paar Daten rein:

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;

geht wunderbar ergebnis:

select * from SUMMEN14TAGE;

ART_NR                 SUMANZAHL              
---------------------- ---------------------- 
5                      5                      
3                      2                      

2 rows selected

Jetzt folgendes:

update TABLE1 set Anzahl=4 where ID=3;
commit;

läuft ohne Fehler durch.

aber Ergbnis immer noch:

select * from SUMMEN14TAGE;

ART_NR                 SUMANZAHL              
---------------------- ---------------------- 
5                      5                      
3                      2                      

2 rows selected

im MLOG$_TABLE1 kein Eintrag vorhanden.

nun noch:

INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('4', '9', 'Draht', '10');
commit;

aber Ergbnis immer noch:

select * from SUMMEN14TAGE;

ART_NR                 SUMANZAHL              
---------------------- ---------------------- 
5                      5                      
3                      2                      

2 rows selected

dafür steht jetzt in MLOG$_TABLE1:

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

Wo liegt denn nur das Problem?

Vielen Dank

[chevyvan2000]

Version vergessen:

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

[chevyvan2000]

Wenn ich direkt nach dem

update TABLE1 set Anzahl=4 where ID=3;
commit;

einen manuellen Refresh ausführe:

alter materialized view SUMMEN14TAGE consider fresh

bekomme ich folgende fehlermeldung:

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

[MPr]

mv_capabilities_table

in diesem Fall können das Package dbms_mview und die mv_capabilities_table weiterhelfen, mit deren Hilfe man eine relativ detaillierte Erläuterung der refresh-Eigenschaften einer MV erhält. Eine Einführung dazu findet man z.B. unter http://www.orafaq.com/node/831.

Im gegebenen Fall fehlen der MView COUNT-Spalten:

-- Anlage der MV mit zusätzlichen COUNT-Spalten
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,
    COUNT(ANZAHL) AS COUNT_ANZAHL,
    COUNT(*) cnt
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;

SQL> INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('1', '5', 'Schraube', '3');

1 Zeile wurde erstellt.

SQL> INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('2', '3', 'Mutter', '2');

1 Zeile wurde erstellt.

SQL> INSERT INTO TABLE1 (ID, ART_NR, NAME, ANZAHL) VALUES ('3', '5', 'Schraube', '2');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from SUMMEN14TAGE;

    ART_NR  SUMANZAHL COUNT_ANZAHL        CNT
---------- ---------- ------------ ----------
         3          2            1          1
         5          5            2          2

SQL> update TABLE1 set Anzahl=4 where ID=3;

1 Zeile wurde aktualisiert.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from SUMMEN14TAGE;

    ART_NR  SUMANZAHL COUNT_ANZAHL        CNT
---------- ---------- ------------ ----------
         3          2            1          1
         5          7            2          2

Gruß

MP

[chevyvan2000]

WOW vielen Dank, so funktioniert es.

Kannst Du nochmal drauf eingehen, warum

COUNT(ANZAHL) AS COUNT_ANZAHL,
COUNT(*) cnt

da mit rein muss, kann ich aus Deinem Link jetzt nicht raus lesen...

nochmal vielen vielen Dank

[MPr]

eine detaillierte Erklärung habe ich gerade nicht gefunden, nur den folgenden Link zur Oracle Doku, der die Einschränkungen aufführt: http://download.oracle.com/docs/cd/B...v.htm#i1007028

Offenbar genügen die Deltas des MView-Logs alleine nicht, um das fast refresh zu ermöglichen.

Gruß

MP