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:
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
);