sqloraclevieworacle19cmaterialized-views

Materialized View on Oracle 19c only refreshes on INSERT INTO, not UPDATE


I created a basic materialized view in my OracleSQL database (version 19c) but it seems to be only updated for each new row added, not when an existing row is updated.

This works:

CREATE MATERIALIZED VIEW TEST1
            BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
AS
SELECT ID,
       ARTICLE_ID,
       STOCK
FROM BOOKING
ORDER BY STOCK;

New rows AND updates on existing rows are getting tracked correctly. However, the following view only updates when a whole row is inserted:

CREATE MATERIALIZED VIEW TEST2
            BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
AS
SELECT ID,
       ARTICLE_ID,
       SUM(STOCK) as STOCK
FROM BOOKING
GROUP BY ID, ARTICLE_ID
ORDER BY SUM(STOCK);

(Yes, I'm aware TEST2 is technically nonsence since GROUP BY ID does nothing but that was the least invasive method I could find to test whether SUM is the culprit.) ID is the only primary key of the BOOKING table. Here is the materialized view log used by both materialized views:

CREATE MATERIALIZED VIEW LOG ON BOOKING
    WITH ROWID, PRIMARY KEY, SEQUENCE (ARTICLE_ID,
                          STOCK)
    INCLUDING NEW VALUES;

Why does TEST2 not work? I'm beginning to feel like aggregate functions don't work by design here. But that would be strange since INSERT INTO is tracked correctly in TEST2, it seems. Plus, I think recalculating something like a SUM, given a materialized view log, should be not that difficult since you already have the delta.

EDIT: Running EXPLAIN_MVIEW on this materialized view gave the following results: enter image description here


Solution

  • exec DBMS_MVIEW.EXPLAIN_MVIEW('TEST2' );
    
    select * from MV_CAPABILITIES_TABLE ;
    

    Will explain you why... And

    CREATE MATERIALIZED VIEW BOOKING_MV
        REFRESH FAST ON COMMIT
    AS
    SELECT 
        ARTICLE_ID, SUM(STOCK) as stk, COUNT(stock) as cs, COUNT(*) as c
    FROM BOOKING
    GROUP BY ARTICLE_ID
    ;
    

    will work...

    NB to avoid searching for the definition in the doc:

    create table mv_capabilities_table (
       statement_id      varchar(30),
       mvowner           varchar(30),
       mvname            varchar(30),
       capability_name   varchar(30),
       possible          character(1),
       related_text      varchar(2000),
       related_num       number,
       msgno             integer,
       msgtxt            varchar(2000),
       seq               number
    );